Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
1  
Urk. There's a great deal of complexity there; it might be worth explaining what the view, and the query, are supposed to actually do. Also, what's your PostgreSQL version? –  Craig Ringer Jan 23 at 8:17
    
Main thing I notice: There's some truly awful row-count estimation; make sure your stats are up to date (ANALYZE;) and consider increasing your statistics targets for table homeprofile and checkpoint. Then throw lots (and lots, and lots) more work_mem at this query. Try SET work_mem = 10MB to start with. Be aware that might use hundreds of MB for this query, so you won't be wanting to run a whole bunch of them at the same time on different conenctions. If the tables and indexes mostly fit in RAM or your storage is fast, consider lowering random_page_cost to (e.g.) 1.2, too. –  Craig Ringer Jan 23 at 8:32
    
Oh, also: Define "poor performance". With numbers. (Noticed another thing: you join on answer_file at least twice, and both times it's quite an expensive join. See if you can avoid that.) –  Craig Ringer Jan 23 at 8:33
    
Version 9.1.9 according to this command root@ubuntu:~# psql --version psql (PostgreSQL) 9.1.9 The purpose of query is to return checkpoint records, with a good number of column values that are based on the child answer column values. –  user2189170 Jan 23 at 8:52
2  
Best to edit the question to add more detail btw. That way you can format it and include more than you can jam in a comment. –  Craig Ringer Jan 23 at 9:01

1 Answer 1

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

share|improve this answer
2  
There is problem in strong correlation between some columns and probably unhappy designed database schema. In this case you should to try significantly redesign a query or you can try break query to more small queries that store output to temporary tables. ANALYZE over temporary tables fixes statistics. Usually can you check some newer Postgresql versions, where some new optimization techniques are enabled. –  Pavel Stehule Jan 25 at 13:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.