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)"
