Re: Weird issue with planner choosing seq scan

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