Window functions speed - Mailing list pgsql-general
| From | Zahir Lalani |
|---|---|
| Subject | Window functions speed |
| Date | |
| Msg-id | DB8PR06MB61879F212C14D631610D1B7BA75D0@DB8PR06MB6187.eurprd06.prod.outlook.com Whole thread Raw |
| Responses |
Re: Window functions speed
(Michael Lewis <mlewis@entrata.com>)
|
| List | pgsql-general |
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
pgsql-general by date: