Обсуждение: Window functions speed
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
I mean, that limit at the top makes me think it is doing a lot less work without the window function, vs examining all candidate rows if we need to count them all. What are you using that count for anyway? Pagination?
Confidential
From: Michael Lewis <mlewis@entrata.com>
Sent: 19 August 2020 20:53
To: Zahir Lalani <ZahirLalani@oliver.agency>
Cc: pgsql-general@postgresql.org
Subject: Re: Window functions speed
Where's the query? Are you able to run explain analyze so we can see estimates vs actual counts? What version are you using?
Can you share explain analyze before and after the window function?
I mean, that limit at the top makes me think it is doing a lot less work without the window function, vs examining all candidate rows if we need to count them all. What are you using that count for anyway? Pagination?
Based on the hint here. We looked at the query and found most of the culprits were with a param that forced all accessible records – it should not have been there in most cases. We have removed that for most users and its fine !