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-24 21:18:02
Message-ID: F0238EBA67824444BC1CB4700960CB4804B0C70B@dmpeints002.isotach.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote
> Sean Leach <sleach(at)wiggum(dot)com> writes:
> > Now - here is prod:
> 
> > db=> select count(1) from u_counts;
> >    count
> > ---------
> >   3292215
> > (1 row)
> 
> 
> >           ->  Seq Scan on u_counts c  (cost=0.00..444744.45  
> > rows=1106691 width=4) (actual time=1429.996..7893.178 rows=1036015  
> > loops=1)
> >                 Filter: (stamp > (now() - '1 day'::interval))
> 
> Given that this scan actually is selecting about a third of the table,
> I'm not sure that the planner is doing the wrong thing.  It's hard to
> see how an indexscan would be an improvement.

If you always get around a third of the rows in your table written in the last day, you've got to be deleting about a third of the rows in your table every day too. You might have a huge number of dead rows in your table, slowing down the sequential scan.
(Likewise updating a third of the rows, changing an indexed field.)

What do you get from:
VACUUM VERBOSE u_counts;

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

Responses

pgsql-performance by date

Next:From: Sean LeachDate: 2008-02-24 23:21:15
Subject: Re: Weird issue with planner choosing seq scan
Previous:From: Sean LeachDate: 2008-02-24 20:28:17
Subject: Re: Weird issue with planner choosing seq scan

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