Skip site navigation (1) Skip section navigation (2)

Re: Weird issue with planner choosing seq scan

From: "Stephen Denne" <Stephen(dot)Denne(at)datamail(dot)co(dot)nz>
To: "Sean Leach" <sleach(at)wiggum(dot)com>
Cc: "pgsql-performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Weird issue with planner choosing seq scan
Date: 2008-02-25 23:19:43
Message-ID: F0238EBA67824444BC1CB4700960CB4804B0C9C1@dmpeints002.isotach.com (view raw or flat)
Thread:
Lists: pgsql-performance
Sean Leach wrote
> On Feb 25, 2008, at 1:19 PM, Stephen Denne wrote:
> >
> >
> > Have you checked Scott Marlowe's note:
> >
> >>> unless you've got a long running transaction
> >
> > How come those 2 million dead rows are not removable yet? My guess  
> > (based on a quick search of the mailing lists) would be that they  
> > were generated from your aggregation run, and that a long running  
> > transaction started before your aggregation run committed 
> (possibly  
> > even before it started), and that transaction is still alive.
> >
> > Alternatively, it may be a different 2 million dead row 
> versions now  
> > than earlier, and may simply be a side effect of your particular  
> > usage, and nothing to worry about. (Though it is exactly the same  
> > number of rows, which strongly hints at being exactly the 
> same rows.)
> 
> 
> Great detective work, you are correct.  We have a daemon that 
> runs and  
> is constantly adding new data to that table, then we aggregated it  
> daily (I said weekly before, I was incorrect) - which deletes 
> several  
> rows as it updates a bunch of others.  So it sounds like upping  
> max_fsm_pages is the best option.

but... do you have a long running transaction? Are you happy having 30% to 40% of your table unusable (needlessly?) and slowing down your sequential scans?

Regards,
Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege.  If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way.

__________________________________________________________________
  This email has been scanned by the DMZGlobal Business Quality 
              Electronic Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
__________________________________________________________________



In response to

pgsql-performance by date

Next:From: Sean LeachDate: 2008-02-25 23:32:10
Subject: Re: Weird issue with planner choosing seq scan
Previous:From: Stephen DenneDate: 2008-02-25 22:59:13
Subject: Re: Weird issue with planner choosing seq scan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group