PostgreSQL’s Query Hierarchical data poor performance

performancepostgresqlpostgresql-9.1postgresql-performance

Badly need some technical assistance on this query performance issue I'm working on.

Summary of the data structure is:
1 project has many homeprofiles, 1 homeprofile has many tasks, 1 task has many checkpoints, 1 checkpoint has many answers, 1 answer has many answer_files

Stuff I did already: indexed all foreign keys, indexed frequently checked values on WHERE clause. Recreated constraints. I think the main flaw
here is the query and the implementation of the view. Problem is the view logic I prepared suits a lot of the required calculations I need on the answer > answer_file level.

Would really appreciate if anyone can point me to the right direction for this one.

Table "pubic.checkpoint"
Column           |            Type             |                      Modifiers                       | Storage  |                          Description                           
---------------------------+-----------------------------+------------------------------------------------------+----------+----------------------------------------------------------------
id                        | int                      | not null default nextval('checkpoint_seq'::regclass) | plain    | Unique primary key
task_id                   | int                      |                                                      | plain    | 
system_checkpoint_id      | int                      |                                                      | plain    | 
title                     | character varying(255)      |                                                      | extended | 
description               | text        |                                                      | extended | 
is_mandatory              | boolean                 |                                                      | plain    | 
status                    | character varying(20)       | default 'New'::character varying                     | extended | 'Acceptable', ,'Not Acceptable','Referred','Referred Response'
date_first_opened         | timestamp                |                                                      | plain    | 
active_at_task_percentage | double precision            |                                                      | plain    | 
date_last_viewed          | timestamp                |                                                      | plain    | 
photos_required           | smallint                    |                                                      | plain    | 
risk_value                | integer                     |                                                      | plain    | 
risk_factor               | smallint                    |                                                      | plain    | 
risk_legal                | real                        |                                                      | plain    | 
ordering                  | smallint                    |                                                      | plain    | 
subcontractor_category_id | integer                     |                                                      | plain    | 
date_created              | timestamp without time zone | not null default now()                               | plain    | 

Referenced by:
TABLE "answer" CONSTRAINT "FK_AW_checkpoint_id_Checkpiont_id" FOREIGN KEY (checkpoint_id) REFERENCES checkpoint(id) ON DELETE CASCADE


Table "pubic.answer"
     Column     |          Type          |                     Modifiers                      | Storage  |                       Description                        
----------------+------------------------+----------------------------------------------------+----------+----------------------------------------------------------
 id             | int                 | not null default nextval('answer_seq'::regclass)   | plain    | 
 user_id        | int                 | not null                                           | plain    | 
 checkpoint_id  | int                 | not null                                           | plain    | 
 date_created   | int           |                                                    | plain    | 
 answer_type    | character varying(55)  |                                                    | extended | The value would be "Yes","No","Ignore"
 note           | text                   |                                                    | extended | 
 ip_address     | character varying(20)  |                                                    | extended | 
 latitude       | character varying(20)  |                                                    | extended | 
 longitude      | character varying(20)  |                                                    | extended | 
 assigned_to    | int                 |                                                    | plain    | Id of building company user to whom request is assigned.
 assigned_type  | int            |                                                    | plain    | 
 view_hash      | character varying(128) |                                                    | extended | 
 date_modified  | int           | default now()                                      | plain    | 
 referred_trait | character varying(40)  | not null default 'Not Referred'::character varying | extended | 
 is_required    | boolean                | default false                                      | plain    | 
 code           | character varying(6)   | default func_generate_answer_code()                | extended | 
Referenced by:
    TABLE "answer_referral" CONSTRAINT "FK_ansreferral_to_answer" FOREIGN KEY (answer_id) REFERENCES answer(id) MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT

Table "public.answer_file"
Column   |          Type          |                     Modifiers                      | Storage  |                              Description                              
------------+------------------------+----------------------------------------------------+----------+-----------------------------------------------------------------------
id         | int                 | not null                                           | plain    | 
answer_id  | int                 |                                                    | plain    | 
filename   | character varying(255) |                                                    | extended | 
path       | character varying(255) |                                                    | extended | 
thumbnail  | character varying(255) |                                                    | extended | thumbnail image filename  of the original photo(generated by iOS app)
status     | character varying(40)  | not null default 'Not Reviewed'::character varying | extended | Possible values are: 'Reviewed' or 'Not Reviewed'
is_visible | boolean                | not null default true                              | plain    | Denotes if photo is visible on certain applications.To be decided.
is_deleted | boolean                | not null default false                             | plain    | Flag for photo deleted state, image is retained in the server
Referenced by:
TABLE "answer_referral_thread_image" CONSTRAINT "FK_ansthreadimg_to_ansfile" FOREIGN KEY (answer_file_id) REFERENCES answer_file(id) MATCH FULL ON UPDATE CASCADE ON DELETE RESTRICT


View definition: view_checkpoint_aggregated_data
CREATE VIEW view_checkpoint_aggregated_data AS SELECT cp.id AS id, 
CASE WHEN 
    EXISTS (SELECT date_modified FROM answer WHERE checkpoint_id = cp.id ORDER BY date_modified DESC LIMIT 1) 
THEN 
    (SELECT date_modified FROM answer WHERE checkpoint_id = cp.id ORDER BY date_modified DESC LIMIT 1)
ELSE cp.date_created END AS date_modified,
(SELECT COUNT(id) FROM answer WHERE checkpoint_id = cp.id) AS answer_count,
CASE
WHEN 
    cp.is_mandatory = true AND (SELECT COUNT(id) FROM answer WHERE checkpoint_id = cp.id) = 0 THEN 'Needs Answers'
WHEN
    (cp.is_mandatory = false OR cp.is_mandatory IS NULL) AND (SELECT COUNT(id) FROM answer WHERE checkpoint_id = cp.id) = 0 THEN 'None'
WHEN 
(SELECT COUNT(id) FROM answer WHERE checkpoint_id = cp.id) = (SELECT COUNT(id) FROM answer WHERE checkpoint_id = cp.id AND answer_type = 'Acceptable')
AND (SELECT COUNT(id) FROM answer WHERE checkpoint_id = cp.id) > 0 THEN 'Acceptable' 
ELSE 'Not Acceptable'
END AS checkpoint_status,
(SELECT COUNT(cp2.id) FROM checkpoint AS cp2 INNER JOIN answer ON cp.id = answer.checkpoint_id INNER JOIN answer_file ON answer.id = answer_file.answer_id WHERE cp2.id = cp.id) AS answer_photo_count,
(SELECT string_agg(DISTINCT(user_id::text),',') FROM answer WHERE answer.checkpoint_id = cp.id GROUP BY answer.checkpoint_id) AS answer_creators,
(SELECT subcontracting_company.name_company FROM company_category 
INNER JOIN company ON company_category.COMPANY_ID=company.ID  
WHERE cp.SUBCONTRACTOR_CATEGORY_ID = company_category.ID) AS checkpoint_trade_responsible
FROM checkpoint AS cp;

Here's my query

SELECT checkpoint.ID,
CASE WHEN view_checkpoint_aggregated_data.ANSWER_PHOTO_COUNT > 0
THEN (SELECT concat('/web/api//getAnswerImage?answer_id=',answer.id,'&thumb=',answer_file.filename) 
    FROM answer INNER JOIN answer_file ON answer.id = answer_file.answer_id WHERE answer.checkpoint_id = checkpoint.ID LIMIT 1)
ELSE '/i/HomeProfileSystem/i_enrollment_main_image.jpg' 
END,
checkpoint.DESCRIPTION,
CASE WHEN category.NAME IS NULL THEN 'NONE' ELSE category.NAME END AS category_name,
task.DESCRIPTION, view_checkpoint_aggregated_data.answer_count, view_checkpoint_aggregated_data.ANSWER_PHOTO_COUNT, checkpoint.PHOTOS_REQUIRED,
view_checkpoint_aggregated_data.CHECKPOINT_STATUS, company.NAME_COMPANY, checkpoint.RISK_VALUE, checkpoint.RISK_FACTOR, checkpoint.RISK_LEGAL,
homeprofile.STREET_LINE1, homeprofile.STATE_CODE, homeprofile.POSTAL_CODE, to_char(view_checkpoint_aggregated_data.DATE_MODIFIED,'MM/DD/YYYY') AS date_modified,
project.NAME, checkpoint.ID
FROM checkpoint
INNER JOIN task ON (checkpoint.TASK_ID=task.ID)
INNER JOIN view_checkpoint_aggregated_data ON (checkpoint.ID=view_checkpoint_aggregated_data.ID)
LEFT JOIN category ON (task.SUBCONTRACTOR_CATEGORY_ID=category.ID)
LEFT JOIN company_category ON (checkpoint.SUBCONTRACTOR_CATEGORY_ID=company_category.ID)
LEFT JOIN company ON (company_category.COMPANY_ID=company.ID)
LEFT JOIN homeprofile ON (task.HOMEPROFILE_ID=homeprofile.ID)
LEFT JOIN project ON (homeprofile.project_ID=project.ID)  LEFT JOIN 
(SELECT answer.checkpoint_id FROM answer LEFT JOIN answer_referral ON answer.id = answer_referral.answer_id WHERE answer.answer_type IN ('New','Not Acceptable','Corrected','Acceptable') GROUP BY checkpoint_id) ans_sub 
ON checkpoint.ID = ans_sub.checkpoint_id WHERE homeprofile.BUILDING_COMPANY_ID=800 AND homeprofile.DIVISION_ID=1206 AND homeprofile.HOMEPROFILE_STATUS_TYPE_CODE<>'CLOSD' AND (homeprofile.ID IN (48206,45399,48205,48207,48412,47677,48113,48379,48343,47947,45400,48118,48119,47656,47657,47415,47658,47235,48414,48415) OR homeprofile.project_ID IS NULL) AND view_checkpoint_aggregated_data.answer_count > 0 AND ans_sub.checkpoint_id IS NOT NULL ORDER BY view_checkpoint_aggregated_data.DATE_MODIFIED DESC

Explain Dump

Sort  (cost=2669.37..2669.38 rows=1 width=200) (actual time=29353.552..29365.930 rows=16610 loops=1)
   Sort Key: (CASE WHEN (SubPlan 12) THEN ((SubPlan 13))::timestamp with time zone ELSE (cp.date_created)::timestamp with time zone END)
   Sort Method: external merge  Disk: 4600kB
   ->  Hash Join  (cost=1954.10..2669.36 rows=1 width=200) (actual time=741.499..29259.443 rows=16610 loops=1)
         Hash Cond: ((public.answer.checkpoint_id)::integer = (checkpoint.id)::integer)
         ->  HashAggregate  (cost=1776.62..1940.27 rows=16365 width=4) (actual time=51.185..60.741 rows=17263 loops=1)
               ->  Hash Left Join  (cost=1.11..1683.58 rows=37216 width=4) (actual time=0.028..35.927 rows=37769 loops=1)
                     Hash Cond: ((public.answer.id)::integer = (answer_referral.answer_id)::integer)
                     ->  Seq Scan on answer  (cost=0.00..1542.86 rows=37216 width=8) (actual time=0.014..19.486 rows=37769 loops=1)
                           Filter: ((checkpoint_id IS NOT NULL) AND ((answer_type)::text = ANY ('{New,"Not Acceptable",Corrected,Acceptable}'::text[])))
                     ->  Hash  (cost=1.05..1.05 rows=5 width=4) (actual time=0.006..0.006 rows=5 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 1kB
                           ->  Seq Scan on answer_referral  (cost=0.00..1.05 rows=5 width=4) (actual time=0.002..0.004 rows=5 loops=1)
         ->  Hash  (cost=177.46..177.46 rows=2 width=200) (actual time=688.453..688.453 rows=16611 loops=1)
               Buckets: 1024  Batches: 4 (originally 1)  Memory Usage: 1025kB
               ->  Nested Loop Left Join  (cost=8.97..177.46 rows=2 width=200) (actual time=7.807..665.720 rows=16611 loops=1)
                     ->  Nested Loop Left Join  (cost=8.97..176.76 rows=2 width=183) (actual time=7.805..609.305 rows=16611 loops=1)
                           ->  Nested Loop  (cost=8.97..176.16 rows=2 width=183) (actual time=7.803..549.717 rows=16611 loops=1)
                                 ->  Nested Loop  (cost=8.97..113.87 rows=5 width=170) (actual time=2.492..74.772 rows=51577 loops=1)
                                       ->  Nested Loop Left Join  (cost=8.97..110.34 rows=1 width=75) (actual time=1.810..3.939 rows=302 loops=1)
                                             ->  Nested Loop Left Join  (cost=8.97..102.06 rows=1 width=64) (actual time=1.806..3.225 rows=302 loops=1)
                                                   ->  Nested Loop  (cost=8.97..101.78 rows=1 width=56) (actual time=1.794..2.601 rows=302 loops=1)
                                                         ->  Bitmap Heap Scan on homeprofile  (cost=8.97..13.01 rows=1 width=37) (actual time=0.075..0.254 rows=20 loops=1)
                                                               Recheck Cond: (((building_company_id)::integer = 800) AND ((division_id)::integer = 1206))
                                                               Filter: (((homeprofile_status_type_code)::bpchar <> 'CLOSD'::bpchar) AND (((id)::integer = ANY ('{48206,45399,48205,48207,48412,47677,48113,48379,48343,47947,45400,48118,4811
9,47656,47657,47415,47658,47235,48414,48415}'::integer[])) OR (subdivision_id IS NULL)))
                                                               ->  BitmapAnd  (cost=8.97..8.97 rows=1 width=0) (actual time=0.060..0.060 rows=0 loops=1)
                                                                     ->  Bitmap Index Scan on idx_homeprofile_building_company_id  (cost=0.00..4.36 rows=14 width=0) (actual time=0.033..0.033 rows=94 loops=1)
                                                                           Index Cond: ((building_company_id)::integer = 800)
                                                                     ->  Bitmap Index Scan on idx_homeprofile_division_id  (cost=0.00..4.36 rows=14 width=0) (actual time=0.022..0.022 rows=22 loops=1)
                                                                           Index Cond: ((division_id)::integer = 1206)
                                                         ->  Index Scan using idx_task_homeprofile_id on task  (cost=0.00..88.28 rows=39 width=27) (actual time=0.087..0.104 rows=15 loops=20)
                                                               Index Cond: ((homeprofile_id)::integer = (homeprofile.id)::integer)
                                                   ->  Index Scan using pk_category on category  (cost=0.00..0.27 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=302)
                                                         Index Cond: (task.subcontractor_category_id = (id)::integer)
                                             ->  Index Scan using pk_subdivision on subdivision  (cost=0.00..8.27 rows=1 width=19) (actual time=0.001..0.001 rows=0 loops=302)
                                                   Index Cond: ((homeprofile.subdivision_id)::integer = (id)::integer)
                                       ->  Index Scan using idx_cp_task_id on checkpoint  (cost=0.00..2.98 rows=44 width=103) (actual time=0.009..0.126 rows=171 loops=302)
                                             Index Cond: ((task_id)::integer = (task.id)::integer)
                                 ->  Index Scan using checkpoint_id_key on checkpoint cp  (cost=0.00..12.45 rows=1 width=13) (actual time=0.008..0.008 rows=0 loops=51577)
                                       Index Cond: ((id)::integer = (checkpoint.id)::integer)
                                       Filter: ((SubPlan 14) > 0)
                                       SubPlan 14
                                         ->  Aggregate  (cost=12.03..12.04 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=51577)
                                               ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=4) (actual time=0.003..0.003 rows=1 loops=51577)
                                                     Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                                                     ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.002..0.002 rows=1 loops=51577)
                                                           Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                           ->  Index Scan using pk_subcontracting_company_category on subcontracting_company_category  (cost=0.00..0.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=16611)
                                 Index Cond: (checkpoint.subcontractor_category_id = (id)::integer)
                     ->  Index Scan using pk_subcontracting_company on subcontracting_company  (cost=0.00..0.33 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=16611)
                           Index Cond: ((subcontracting_company_category.subcontracting_company_id)::integer = (id)::integer)
         SubPlan 1
           ->  Aggregate  (cost=71.49..71.50 rows=1 width=4) (actual time=0.802..0.802 rows=1 loops=16610)    
                 ->  Nested Loop  (cost=12.05..71.49 rows=1 width=4) (actual time=0.800..0.801 rows=0 loops=16610)
                       ->  Hash Join  (cost=12.05..63.19 rows=1 width=0) (actual time=0.799..0.799 rows=0 loops=16610)
                             Hash Cond: ((public.answer_file.answer_id)::integer = (public.answer.id)::integer)
                             ->  Seq Scan on answer_file  (cost=0.00..44.28 rows=1828 width=4) (actual time=0.002..0.367 rows=1828 loops=16610)
                             ->  Hash  (cost=12.02..12.02 rows=2 width=4) (actual time=0.010..0.010 rows=2 loops=16610)
                                   Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                   ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=4) (actual time=0.007..0.007 rows=2 loops=16610)
                                         Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                                         ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.004..0.004 rows=2 loops=16610)
                                               Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                       ->  Index Scan using checkpoint_id_key on checkpoint cp2  (cost=0.00..8.29 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=21)
                             Index Cond: ((id)::integer = (cp.id)::integer)
         SubPlan 2
           ->  Limit  (cost=12.05..63.19 rows=1 width=10) (actual time=0.452..0.453 rows=1 loops=11)
                 ->  Hash Join  (cost=12.05..63.19 rows=1 width=10) (actual time=0.449..0.449 rows=1 loops=11)
                       Hash Cond: ((public.answer_file.answer_id)::integer = (public.answer.id)::integer)
                       ->  Seq Scan on answer_file  (cost=0.00..44.28 rows=1828 width=10) (actual time=0.001..0.197 rows=1109 loops=11)
                       ->  Hash  (cost=12.02..12.02 rows=2 width=4) (actual time=0.010..0.010 rows=2 loops=11)
                             Buckets: 1024  Batches: 1  Memory Usage: 1kB
                             ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=4) (actual time=0.006..0.007 rows=2 loops=11)
                                   Recheck Cond: ((checkpoint_id)::integer = (checkpoint.id)::integer)
                                   ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.003..0.003 rows=2 loops=11)
                                         Index Cond: ((checkpoint_id)::integer = (checkpoint.id)::integer)
         SubPlan 3
           ->  Aggregate  (cost=12.03..12.04 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=16610)
                 ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=4) (actual time=0.006..0.007 rows=2 loops=16610)
                       Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                       ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.004..0.004 rows=2 loops=16610)
                             Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
         SubPlan 4
           ->  Aggregate  (cost=71.49..71.50 rows=1 width=4) (actual time=0.809..0.810 rows=1 loops=16610)
                 ->  Nested Loop  (cost=12.05..71.49 rows=1 width=4) (actual time=0.808..0.808 rows=0 loops=16610)
                       ->  Hash Join  (cost=12.05..63.19 rows=1 width=0) (actual time=0.806..0.806 rows=0 loops=16610)
                             Hash Cond: ((public.answer_file.answer_id)::integer = (public.answer.id)::integer)
                             ->  Seq Scan on answer_file  (cost=0.00..44.28 rows=1828 width=4) (actual time=0.001..0.372 rows=1828 loops=16610)
                             ->  Hash  (cost=12.02..12.02 rows=2 width=4) (actual time=0.007..0.007 rows=2 loops=16610)
                                   Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                   ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=4) (actual time=0.004..0.005 rows=2 loops=16610)
                                         Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                                         ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.002..0.002 rows=2 loops=16610)
                                               Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                       ->  Index Scan using checkpoint_id_key on checkpoint cp2  (cost=0.00..8.29 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=21)
                             Index Cond: ((id)::integer = (cp.id)::integer)
         SubPlan 5
           ->  Aggregate  (cost=12.03..12.04 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=16588)
                 ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=4) (actual time=0.006..0.007 rows=2 loops=16588)
                       Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                       ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.003..0.003 rows=2 loops=16588)
                             Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
         SubPlan 6
           ->  Aggregate  (cost=12.03..12.04 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=22)
                 ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=4) (actual time=0.007..0.007 rows=2 loops=22)
                       Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                       ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.004..0.004 rows=2 loops=22)
                             Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
         SubPlan 7
           ->  Aggregate  (cost=12.03..12.04 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=16610)
                 ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=4) (actual time=0.004..0.004 rows=2 loops=16610)
                       Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                       ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.002..0.002 rows=2 loops=16610)
                             Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
         SubPlan 8
           ->  Aggregate  (cost=12.03..12.04 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=16610)
                 ->  Bitmap Heap Scan on answer  (cost=4.27..12.03 rows=2 width=4) (actual time=0.004..0.005 rows=2 loops=16610)
                       Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                       Filter: ((answer_type)::text = 'Acceptable'::text)
                       ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.002..0.002 rows=2 loops=16610)
                             Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
         SubPlan 9
           ->  Aggregate  (cost=12.03..12.04 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=16596)
                 ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=4) (actual time=0.004..0.004 rows=2 loops=16596)
                       Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                       ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.002..0.002 rows=2 loops=16596)
                             Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
         SubPlan 10
           ->  Limit  (cost=12.03..12.03 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=16610)
                 ->  Sort  (cost=12.03..12.04 rows=2 width=8) (actual time=0.010..0.010 rows=1 loops=16610)
                       Sort Key: public.answer.date_modified
                       Sort Method: quicksort  Memory: 17kB
                       ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=8) (actual time=0.004..0.005 rows=2 loops=16610)
                             Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                             ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.002..0.002 rows=2 loops=16610)
                                   Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
         SubPlan 11
           ->  Limit  (cost=12.03..12.03 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=16610)
                 ->  Sort  (cost=12.03..12.04 rows=2 width=8) (actual time=0.008..0.008 rows=1 loops=16610)
                       Sort Key: public.answer.date_modified
                       Sort Method: quicksort  Memory: 17kB
                       ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=8) (actual time=0.004..0.005 rows=2 loops=16610)
                             Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                             ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.002..0.002 rows=2 loops=16610)
                                   Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
         SubPlan 12
           ->  Limit  (cost=12.03..12.03 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=16610)
                 ->  Sort  (cost=12.03..12.04 rows=2 width=8) (actual time=0.009..0.009 rows=1 loops=16610)
                       Sort Key: public.answer.date_modified
                       Sort Method: quicksort  Memory: 17kB
                       ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=8) (actual time=0.004..0.005 rows=2 loops=16610)
                             Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                             ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.003..0.003 rows=2 loops=16610)
                                   Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)
         SubPlan 13
           ->  Limit  (cost=12.03..12.03 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=16610)
                 ->  Sort  (cost=12.03..12.04 rows=2 width=8) (actual time=0.008..0.008 rows=1 loops=16610)
                       Sort Key: public.answer.date_modified
                       Sort Method: quicksort  Memory: 17kB
                       ->  Bitmap Heap Scan on answer  (cost=4.27..12.02 rows=2 width=8) (actual time=0.004..0.005 rows=2 loops=16610)
                             Recheck Cond: ((checkpoint_id)::integer = (cp.id)::integer)
                             ->  Bitmap Index Scan on idx_answer_checkpoint_id  (cost=0.00..4.27 rows=2 width=0) (actual time=0.002..0.002 rows=2 loops=16610)
                                   Index Cond: ((checkpoint_id)::integer = (cp.id)::integer)

Best Answer

Made some progress with the optimization, I just scrapped the view and replaced it with a stored function instead. I applied increased statistics on answer, checkpoint, and homeprofile tables(inreased from 100-500) and I don't notice any execution time decrease unfortunately. :

CREATE OR REPLACE FUNCTION func_checkpoint_aggregated_data(cp_id int) RETURNS 
    TABLE(cp_date_modified timestamp, cp_status varchar, cp_answer_count int) AS $$
DECLARE
    v_date_modified timestamp;
    v_answer_count int;
    v_cp_status varchar;
    v_cp_is_mandatory boolean;
    v_cp_acceptable_flag boolean;
    ans RECORD;
BEGIN
    SELECT is_mandatory FROM checkpoint WHERE id = cp_id INTO v_cp_is_mandatory;
    v_answer_count := 0;
    v_cp_status := '';
    v_cp_acceptable_flag := true;
FOR ans IN SELECT answer.checkpoint_id, answer.date_modified, answer.answer_type, answer_file.id, answer_file.answer_id, answer_file.filename FROM answer LEFT JOIN answer_file ON answer_file.answer_id = answer.id WHERE answer.checkpoint_id  = cp_id ORDER BY answer.date_modified  LOOP
    v_answer_count := v_answer_count + 1;
    /* since it's sorted the date_modified will always be the first*/
    IF v_answer_count = 1 THEN
        v_date_modified := ans.date_modified;
    END IF;
    IF ans.answer_type IS NULL OR ans.answer_type != 'Acceptable' THEN
        v_cp_acceptable_flag := false;
    END IF;
END LOOP;

    IF v_cp_is_mandatory = true AND v_answer_count = 0 THEN
        v_cp_status := 'Needs Answers';
    ELSEIF (v_cp_is_mandatory = false OR v_cp_is_mandatory IS NULL) AND v_answer_count = 0 THEN
        v_cp_status := 'None';
    ELSEIF v_answer_count > 0 AND v_cp_acceptable_flag = true THEN
        v_cp_status := 'Acceptable';
    ELSE
        v_cp_status := 'Not Acceptable';
    END IF;

    RETURN QUERY SELECT v_date_modified, v_cp_status, v_answer_count;
END;
$$ LANGUAGE plpgsql;

Updated query

SELECT checkpoint.ID, checkpoint.DESCRIPTION,
CASE WHEN category.NAME IS NULL THEN 'NONE' ELSE category.NAME END AS category_name,
    task.DESCRIPTION, checkpoint.PHOTOS_REQUIRED,
    subcontracting_company.NAME_COMPANY, checkpoint.RISK_VALUE, checkpoint.RISK_FACTOR, checkpoint.RISK_LEGAL,
    homeprofile.STREET_LINE1, homeprofile.STATE_CODE, homeprofile.POSTAL_CODE, 
    project.NAME, checkpoint.ID, func_checkpoint_aggregated_data(checkpoint.ID)
FROM checkpoint
    INNER JOIN task ON (checkpoint.TASK_ID=task.ID)
    LEFT JOIN category ON (task.SUBCONTRACTOR_CATEGORY_ID=category.ID)
    LEFT JOIN subcontracting_company_category ON (checkpoint.SUBCONTRACTOR_CATEGORY_ID=subcontracting_company_category.ID)
    LEFT JOIN subcontracting_company ON (subcontracting_company_category.SUBCONTRACTING_COMPANY_ID=subcontracting_company.ID)
    LEFT JOIN homeprofile ON (task.HOMEPROFILE_ID=homeprofile.ID)
    LEFT JOIN project ON (homeprofile.project_ID=project.ID)  
    LEFT JOIN (SELECT answer.checkpoint_id FROM answer LEFT JOIN answer_referral ON answer.id = answer_referral.answer_id WHERE answer.answer_type IN ('New','Not Acceptable','Corrected','Acceptable') GROUP BY checkpoint_id) ans_sub ON checkpoint.ID = ans_sub.checkpoint_id 
    WHERE homeprofile.BUILDING_COMPANY_ID=800 AND homeprofile.DIVISION_ID=1206 AND homeprofile.HOMEPROFILE_STATUS_TYPE_CODE<>'CLOSD' AND (homeprofile.ID IN (48206,45399,48205,48207,48412,47677,48113,48379,48343,47947,45400,48118,48119,47656,47657,47415,47658,47235,48414,48415) 
    OR homeprofile.project_ID IS NULL) 
    AND ans_sub.checkpoint_id IS NOT NULL

Query execution time reduced by approx. 80% but it's still not good enough because exec time is 6079 ms, for the records I'm querying(approx. 50,000+ checkpoints)

Link to explain analyze results:

http://explain.depesz.com/s/lq8

Related Question