Prev: [HACKERS] HS starting snapshot corrections
Next: Query execution plan from 8.3 -> 8.4
From: "Brendan Hill" on 10 May 2010 04:09
Getting significantly lower performance on a specific query after upgrading
from 8.3 -> 8.4 (windows). I'm not expecting a quick fix from the mail
lists, but I would appreciate any indications as to where else I could look
or what tools I could employ to investigae further. Details below.



-Brendan







Upgraded Windows installation on the weekend from 8.3 to 8.4 - database
schema & indexes etc are identical, reindex, analyze and cluster all
performed yesterday. Most of the performance settings in postgres.conf were
*increased* ie. memory allocations etc an server is generally running fine.
default_statistics_target went from 10->100.



The following query went from 5-10 second in 8.3 (same for master and
slave), to 45-60 seconds in 8.4 (same for master and slave):



SELECT Note_ID, Notes.Note_Type_ID, Note_Types.Description as
Note_Type_Description, Notes.Note_Priority_ID, Note_Priorities.Description
as Note_Priority_Description, Note_Date, Text, User_Name,
Notes.Date_Created, datediff_hh( Notes.Date_Created, GETDATE()) as Hours_Old

FROM Notes

INNER JOIN jbAccounts ON Notes.Created_By = jbAccounts.jbAccount_ID

LEFT OUTER JOIN Note_Types ON Notes.Note_Type_ID = Note_Types.Note_Type_ID

LEFT OUTER JOIN Note_Priorities ON Notes.Note_Priority_ID =
Note_Priorities.Note_Priority_ID

WHERE Notes.Person_ID IN (4315565) AND Notes.Person_ID IN (SELECT
ISNULL(Personnel.Person_ID, Businesses.Main_Person_ID) FROM Businesses LEFT
OUTER JOIN Personnel ON Businesses.Business_ID = Personnel.Business_ID WHERE
( Businesses.Admin_Office_ID IN (1, 4, 8, 5, 9, 6, 7, 2, 3, 10)))

ORDER BY Notes.Date_Created;



(Note - it employs sub-queries for automatic application of security,
changing this is possible but a major restructure of the software. Improving
the query execution planning should be possible as it was fine in 8.3)







Query planner went from:



"Sort (cost=4583.19..4583.27 rows=30 width=146)"

" Sort Key: notes.date_created"

" -> Nested Loop (cost=0.00..4582.45 rows=30 width=146)"

" -> Nested Loop Left Join (cost=0.00..4485.29 rows=30 width=145)"

" -> Nested Loop Semi Join (cost=0.00..4470.89 rows=30
width=133)"

" -> Nested Loop Left Join (cost=0.00..61.15 rows=30
width=137)"

" -> Index Scan using notes_person_id on notes
(cost=0.00..46.75 rows=30 width=131)"

" Index Cond: (person_id = 4315565)"

" -> Index Scan using note_priorities_pkey on
note_priorities (cost=0.00..0.47 rows=1 width=10)"

" Index Cond: (notes.note_priority_id =
note_priorities.note_priority_id)"

" -> Subquery Scan "ANY_subquery" (cost=0.00..23735.89
rows=322 width=4)"

" Filter: ("ANY_subquery"."isnull" = 4315565)"

" -> Merge Left Join (cost=0.00..22930.02
rows=64470 width=8)"

" Merge Cond: (businesses.business_id =
personnel.business_id)"

" -> Index Scan using businesses_pkey on
businesses (cost=0.00..5546.18 rows=64470 width=8)"

" Filter: (admin_office_id = ANY
('{1,4,8,5,9,6,7,2,3,10}'::integer[]))"

" -> Index Scan using personnel_business_id
on personnel (cost=0.00..781.32 rows=25907 width=8)"

" -> Index Scan using note_types_pkey on note_types
(cost=0.00..0.47 rows=1 width=16)"

" Index Cond: (notes.note_type_id =
note_types.note_type_id)"

" -> Index Scan using jbaccounts_pkey on jbaccounts
(cost=0.00..3.20 rows=1 width=9)"

" Index Cond: (jbaccounts.jbaccount_id = notes.created_by)"





To:



"Sort (cost=28558.95..28560.74 rows=714 width=146)"

" Sort Key: notes.date_created"

" -> Nested Loop IN Join (cost=26636.50..28525.11 rows=714 width=146)"

" -> Hash Left Join (cost=337.30..2193.78 rows=714 width=150)"

" Hash Cond: (notes.note_type_id = note_types.note_type_id)"

" -> Hash Left Join (cost=335.91..2188.03 rows=714
width=138)"

" Hash Cond: (notes.note_priority_id =
note_priorities.note_priority_id)"

" -> Hash Join (cost=334.82..2183.62 rows=714
width=132)"

" Hash Cond: (notes.created_by =
jbaccounts.jbaccount_id)"

" -> Index Scan using notes_person_id on notes
(cost=0.00..1834.48 rows=718 width=130)"

" Index Cond: (person_id = 4315565)"

" -> Hash (cost=258.81..258.81 rows=6081
width=10)"

" -> Seq Scan on jbaccounts
(cost=0.00..258.81 rows=6081 width=10)"

" -> Hash (cost=1.04..1.04 rows=4 width=10)"

" -> Seq Scan on note_priorities (cost=0.00..1.04
rows=4 width=10)"

" -> Hash (cost=1.17..1.17 rows=17 width=16)"

" -> Seq Scan on note_types (cost=0.00..1.17 rows=17
width=16)"

" -> Materialize (cost=26299.20..26302.42 rows=322 width=4)"

" -> Subquery Scan "IN_subquery" (cost=1.22..26298.88
rows=322 width=4)"

" Filter: ("IN_subquery"."isnull" = 4315565)"

" -> Merge Left Join (cost=1.22..25494.35 rows=64362
width=8)"

" Merge Cond: (businesses.business_id =
personnel.business_id)"

" -> Index Scan using businesses_pkey on
businesses (cost=0.00..8062.90 rows=64362 width=8)"

" Filter: (admin_office_id = ANY
('{1,4,8,5,9,6,7,2,3,10}'::integer[]))"

" -> Index Scan using personnel_business_id on
personnel (cost=0.00..917.83 rows=25855 width=8)"





 | 
Pages: 1
Prev: [HACKERS] HS starting snapshot corrections
Next: Query execution plan from 8.3 -> 8.4