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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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