<div class="Section1"><p class="MsoNormal">Getting significantly lower performance on a specific query after upgrading
from8.3 -> 8.4 (windows). <span style="color:#1F497D">I’m not expecting a quick fix from the mail lists, but I would
appreciateany indications as to where else I could look or what tools I could employ to investigae further. Details
below.</span><pclass="MsoNormal"><span style="color:#1F497D"> </span><p class="MsoNormal"><span
style="color:#1F497D">-Brendan</span><pclass="MsoNormal"><span style="color:#1F497D"> </span><div
style="mso-element:para-border-div;border:none;border-bottom:doublewindowtext 2.25pt;
padding:0cm 0cm 1.0pt 0cm"><p class="MsoNormal" style="border:none;padding:0cm"><span
style="color:#1F497D"> </span></div><pclass="MsoNormal"> <p class="MsoNormal">Upgraded Windows installation on the
weekendfrom 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
generallyrunning fine. default_statistics_target went from 10->100. <p class="MsoNormal"> <p class="MsoNormal">The
followingquery 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):<pclass="MsoNormal"> <p class="MsoNormal">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<p class="MsoNormal">FROM
Notes<p class="MsoNormal">INNER JOIN jbAccounts ON Notes.Created_By = jbAccounts.jbAccount_ID <p class="MsoNormal">LEFT
OUTERJOIN Note_Types ON Notes.Note_Type_ID = Note_Types.Note_Type_ID <p class="MsoNormal">LEFT OUTER JOIN
Note_PrioritiesON Notes.Note_Priority_ID = Note_Priorities.Note_Priority_ID <p class="MsoNormal">WHERE Notes.Person_ID
IN(4315565) AND Notes.Person_ID IN (SELECT ISNULL(Personnel.Person_ID, Businesses.Main_Person_ID) FROM Businesses LEFT
OUTERJOIN Personnel ON Businesses.Business_ID = Personnel.Business_ID WHERE ( Businesses.Admin_Office_ID IN (1, 4, 8,
5,9, 6, 7, 2, 3, 10)))<p class="MsoNormal">ORDER BY Notes.Date_Created;<p class="MsoNormal"> <p class="MsoNormal"><span
style="color:#1F497D">(Note– it employs sub-queries for automatic application of security, changing this is possible
buta major restructure of the software. Improving the query execution planning should be possible as it was fine in
8.3)</span><pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal">Query planner went
from:<pclass="MsoNormal"> <p class="MsoNormal">"Sort (cost=4583.19..4583.27 rows=30 width=146)"<p class="MsoNormal">"
SortKey: notes.date_created"<p class="MsoNormal">" -> Nested Loop (cost=0.00..4582.45 rows=30 width=146)"<p
class="MsoNormal">" -> Nested Loop Left Join (cost=0.00..4485.29 rows=30 width=145)"<p
class="MsoNormal">" -> Nested Loop Semi Join (cost=0.00..4470.89 rows=30 width=133)"<p
class="MsoNormal">" -> Nested Loop Left Join (cost=0.00..61.15 rows=30 width=137)"<p
class="MsoNormal">" -> Index Scan using notes_person_id on notes (cost=0.00..46.75 rows=30
width=131)"<pclass="MsoNormal">" Index Cond: (person_id = 4315565)"<p
class="MsoNormal">" -> Index Scan using note_priorities_pkey on note_priorities
(cost=0.00..0.47rows=1 width=10)"<p class="MsoNormal">" Index Cond:
(notes.note_priority_id= note_priorities.note_priority_id)"<p class="MsoNormal">" -> Subquery
Scan"ANY_subquery" (cost=0.00..23735.89 rows=322 width=4)"<p class="MsoNormal">" Filter:
("ANY_subquery"."isnull"= 4315565)"<p class="MsoNormal">" -> Merge Left Join
(cost=0.00..22930.02rows=64470 width=8)"<p class="MsoNormal">" Merge Cond:
(businesses.business_id= personnel.business_id)"<p class="MsoNormal">" -> Index Scan
usingbusinesses_pkey on businesses (cost=0.00..5546.18 rows=64470 width=8)"<p class="MsoNormal">"
Filter:(admin_office_id = ANY ('{1,4,8,5,9,6,7,2,3,10}'::integer[]))"<p
class="MsoNormal">" -> Index Scan using personnel_business_id on personnel
(cost=0.00..781.32rows=25907 width=8)"<p class="MsoNormal">" -> Index Scan using note_types_pkey on
note_types (cost=0.00..0.47 rows=1 width=16)"<p class="MsoNormal">" Index Cond: (notes.note_type_id
=note_types.note_type_id)"<p class="MsoNormal">" -> Index Scan using jbaccounts_pkey on jbaccounts
(cost=0.00..3.20rows=1 width=9)"<p class="MsoNormal">" Index Cond: (jbaccounts.jbaccount_id =
notes.created_by)"<pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal">To:<p class="MsoNormal"> <p
class="MsoNormal">"Sort (cost=28558.95..28560.74 rows=714 width=146)"<p class="MsoNormal">" Sort Key:
notes.date_created"<pclass="MsoNormal">" -> Nested Loop IN Join (cost=26636.50..28525.11 rows=714 width=146)"<p
class="MsoNormal">" -> Hash Left Join (cost=337.30..2193.78 rows=714 width=150)"<p
class="MsoNormal">" Hash Cond: (notes.note_type_id = note_types.note_type_id)"<p
class="MsoNormal">" -> Hash Left Join (cost=335.91..2188.03 rows=714 width=138)"<p
class="MsoNormal">" Hash Cond: (notes.note_priority_id = note_priorities.note_priority_id)"<p
class="MsoNormal">" -> Hash Join (cost=334.82..2183.62 rows=714 width=132)"<p
class="MsoNormal">" Hash Cond: (notes.created_by = jbaccounts.jbaccount_id)"<p
class="MsoNormal">" -> Index Scan using notes_person_id on notes (cost=0.00..1834.48
rows=718width=130)"<p class="MsoNormal">" Index Cond: (person_id = 4315565)"<p
class="MsoNormal">" -> Hash (cost=258.81..258.81 rows=6081 width=10)"<p
class="MsoNormal">" -> Seq Scan on jbaccounts (cost=0.00..258.81 rows=6081
width=10)"<pclass="MsoNormal">" -> Hash (cost=1.04..1.04 rows=4 width=10)"<p
class="MsoNormal">" -> Seq Scan on note_priorities (cost=0.00..1.04 rows=4 width=10)"<p
class="MsoNormal">" -> Hash (cost=1.17..1.17 rows=17 width=16)"<p
class="MsoNormal">" -> Seq Scan on note_types (cost=0.00..1.17 rows=17 width=16)"<p
class="MsoNormal">" -> Materialize (cost=26299.20..26302.42 rows=322 width=4)"<p
class="MsoNormal">" -> Subquery Scan "IN_subquery" (cost=1.22..26298.88 rows=322 width=4)"<p
class="MsoNormal">" Filter: ("IN_subquery"."isnull" = 4315565)"<p
class="MsoNormal">" -> Merge Left Join (cost=1.22..25494.35 rows=64362 width=8)"<p
class="MsoNormal">" Merge Cond: (businesses.business_id = personnel.business_id)"<p
class="MsoNormal">" -> Index Scan using businesses_pkey on businesses (cost=0.00..8062.90
rows=64362width=8)"<p class="MsoNormal">" Filter: (admin_office_id = ANY
('{1,4,8,5,9,6,7,2,3,10}'::integer[]))"<pclass="MsoNormal">" -> Index Scan using
personnel_business_idon personnel (cost=0.00..917.83 rows=25855 width=8)"<p class="MsoNormal"> <p
class="MsoNormal"> </div>