Confidential Hello
I have spent the last day optimising a critical query which suddenly started
behaving very inefficiently. There were issues with the query which are now
sorted. The base query is now working in a timeframe that is far better.
However, as soon as I add a window count function to the column list, the
performance drops significantly.
Happy to provide info as needed, not sure what is best to provide. Below is the
explain for the query with the window function
Limit (cost=49.44..4580.73 rows=24 width=567)
CTE searched_jobs
-> Unique (cost=0.04..0.05 rows=2 width=4)
-> Sort (cost=0.04..0.05 rows=2 width=4)
Sort Key: id
-> Append (cost=0.00..0.03 rows=2 width=4)
-> Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
-> Result (cost=0.00..0.01 rows=1 width=4)
CTE workflow_and_parentmedia_local_id
-> Function Scan on c_type_by_key_get (cost=0.25..10.25 rows=1000 width=8)
-> WindowAgg (cost=39.14..4570.43 rows=24 width=567)
-> Hash Left Join (cost=39.14..4569.77 rows=24 width=438)
Hash Cond: (j.fk_production_status = wf.workflow_local_id)
Join Filter: ((wf.media_local_id = j.fk_media_type_main) OR
(wf.media_local_id = j.fk_media_type_sub))
-> Nested Loop Left Join (cost=6.64..4532.53 rows=24 width=450)
Filter: ((j.fk_owning_agency_org = ANY
('{26803,34179,24711,24710,32369,21404,20220,13117,4145,4144,20238,32696,32372,21277,22229,21261,7909,15068,14618,34469,34425,34471,34470,11121,1293,20,21384,13281,13438}'::integer[]))
OR (j.fk_agency_org = ANY
('{26803,34179,24711,24710,32369,21404,20220,13117,4145,4144,20238,32696,32372,21277,22229,21261,7909,15068,14618,34469,34425,34471,34470,11121,1293,20,21384,13281,13438}'::integer[]))
OR (job_people.fk_child_id = ANY
('{15264,12190,12189,12191,12192,15262,15263,15265,19317,27243,31746,31773}'::integer[])))
-> Nested Loop Left Join (cost=6.21..4438.89 rows=58
width=454)
-> Nested Loop (cost=5.92..4420.75 rows=58
width=441)
-> Nested Loop Left Join (cost=5.63..4402.61
rows=58 width=428)
-> Nested Loop Left Join
(cost=5.35..4385.16 rows=58 width=416)
-> Nested Loop Left Join
(cost=5.07..4368.19 rows=58 width=406)
Join Filter:
(production_colours.local_id = prod_status.colour_id)
-> Nested Loop Left Join
(cost=4.80..4356.82 rows=58 width=397)
Join Filter:
(prod_status.parent_id = media.id)
-> Nested Loop Left
Join (cost=4.52..4325.64 rows=58 width=380)
-> Nested Loop
Left Join (cost=3.98..4282.28 rows=58 width=376)
Join
Filter: (colours.local_id = js.colour_id)
-> Nested
Loop Left Join (cost=3.71..4270.92 rows=58 width=367)
->
Nested Loop Left Join (cost=3.42..4252.78 rows=58 width=354)
Join Filter: (sub_mt.parent_id = mt.id)
-> Nested Loop Left Join (cost=3.15..4224.50 rows=58 width=341)
Join Filter: (mt.local_id = j.fk_media_type_main)
-> Nested Loop (cost=2.87..4205.54 rows=58 width=320)
-> Nested Loop (cost=2.58..4187.67 rows=58 width=320)
-> Nested Loop (cost=2.29..4169.81 rows=58 width=320)
-> Nested Loop (cost=2.00..4151.66 rows=58 width=301)
-> Nested Loop (cost=1.71..4132.36 rows=58
width=282)
-> Nested Loop (cost=1.42..4114.21
rows=58 width=263)
-> Nested Loop (cost=1.13..3970.01
rows=212 width=213)
-> Nested Loop
(cost=0.70..1408.87 rows=1751 width=209)
Join Filter: (jt.local_id
= j.fk_job_type)
-> Nested Loop
(cost=0.28..52.86 rows=1 width=38)
Join Filter: (jt.id
= js.parent_id)
-> Seq Scan on
c_types js (cost=0.00..24.05 rows=76 width=25)
Filter:
((local_id <> 5) AND (fk_type_def = 4))
-> Materialize
(cost=0.28..8.33 rows=18 width=21)
-> Nested
Loop (cost=0.28..8.24 rows=18 width=21)
-> CTE
Scan on searched_jobs (cost=0.00..0.04 rows=2 width=0)
->
Materialize (cost=0.28..8.00 rows=9 width=21)
-> Index Scan using "iCTypesCTypeDefs" on c_types jt (cost=0.28..7.96 rows=9
width=21)
Index Cond: (fk_type_def = 3)
-> Index Scan using
"iStatus" on jobs j (cost=0.42..1088.23 rows=21422 width=179)
Index Cond:
(fk_status = js.local_id)
Filter:
((is_deleted IS FALSE) AND (fk_parent_id IS NULL) AND (is_template IS FALSE)
AND (fk_job_context_type = 1))
-> Index Scan using
"iRelationshipModuleChild" on relationship_module (cost=0.43..1.45 rows=1
width=8)
Index Cond: (fk_child_id
= j.id)
Filter:
((fk_child_entity_id = 2) AND (fk_parent_entity_id = 1))
-> Index Scan using planning_pkey on
planning campaign (cost=0.29..0.68 rows=1 width=54)
Index Cond: (id =
relationship_module.fk_parent_id)
Filter: ((fk_status <> 1502)
AND (fk_status <> 1504) AND (fk_status <> 1506))
-> Index Scan using organisation_pkey on
organisation cust (cost=0.29..0.31 rows=1 width=23)
Index Cond: (id =
j.fk_owner_parent_org)
-> Index Scan using organisation_pkey on
organisation client (cost=0.29..0.33 rows=1 width=23)
Index Cond: (id = campaign.fk_owner_org)
-> Index Scan using organisation_pkey on organisation
agy (cost=0.29..0.31 rows=1 width=23)
Index Cond: (id = j.fk_agency_owner_org)
-> Index Only Scan using organisation_pkey on organisation
production_agency (cost=0.29..0.31 rows=1 width=4)
Index Cond: (id = j.fk_agency_org)
-> Index Only Scan using organisation_pkey on organisation
owning_agency (cost=0.29..0.31 rows=1 width=4)
Index Cond: (id = j.fk_owning_agency_org)
-> Materialize (cost=0.28..11.15 rows=9 width=21)
-> Index Scan using "iCTypesCTypeDefs" on c_types mt
(cost=0.28..11.11 rows=9 width=21)
Index Cond: (fk_type_def = 2)
Filter: (parent_id IS NULL)
-> Index Scan using "iCtypesLocal" on c_types sub_mt (cost=0.28..0.48 rows=1
width=21)
Index Cond: (local_id = j.fk_media_type_sub)
Filter: (fk_type_def = 2)
->
Index Scan using person_pkey on person (cost=0.29..0.31 rows=1 width=17)
Index Cond: (id = j.fk_job_assignee)
->
Materialize (cost=0.28..6.16 rows=6 width=17)
->
Index Scan using "iCTypesCTypeDefs" on c_types colours (cost=0.28..6.13 rows=6
width=17)
Index Cond: (fk_type_def = 26)
-> Index Scan
using "iCtypesLocal" on c_types media (cost=0.54..0.74 rows=1 width=8)
Index Cond:
(local_id = CASE WHEN (j.fk_media_type_sub = 0) THEN j.fk_media_type_main ELSE
(SubPlan 3) END)
Filter:
(fk_type_def = 2)
SubPlan 3
->
Function Scan on c_type_production_list_exists (cost=0.25..0.26 rows=1 width=4)
-> Index Scan using
"iCtypesLocal" on c_types prod_status (cost=0.28..0.48 rows=5 width=25)
Index Cond:
(local_id = j.fk_production_status)
Filter:
(fk_type_def = 29)
-> Materialize
(cost=0.28..6.16 rows=6 width=17)
-> Index Scan using
"iCTypesCTypeDefs" on c_types production_colours (cost=0.28..6.13 rows=6
width=17)
Index Cond:
(fk_type_def = 26)
-> Index Scan using
media_owner_pkey on media_owner mediaowner (cost=0.28..0.29 rows=1 width=14)
Index Cond: (id =
j.media_owner_id)
-> Index Scan using media_format_pkey on
media_format mediaformat (cost=0.28..0.30 rows=1 width=16)
Index Cond: (id = j.media_format_id)
-> Index Scan using person_pkey on person
createdby (cost=0.29..0.31 rows=1 width=17)
Index Cond: (id = j.fk_created_by)
-> Index Scan using person_pkey on person
projectmanager (cost=0.29..0.31 rows=1 width=17)
Index Cond: (id = j.fk_project_manager)
-> Index Scan using "iRelationshipModuleParent" on
relationship_module job_people (cost=0.43..1.52 rows=1 width=8)
Index Cond: (j.id = fk_parent_id)
Filter: ((fk_parent_entity_id = 2) AND
(fk_child_entity_id = 6))
-> Hash (cost=20.00..20.00 rows=1000 width=8)
-> CTE Scan on workflow_and_parentmedia_local_id wf
(cost=0.00..20.00 rows=1000 width=8)
Thx
Z
