Query execution plan from 8.3 -> 8.4 - Mailing list pgsql-hackers

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