Re: Index not being used unless enable_seqscan=false

On Wed, 2005-08-10 at 13:31 -0700, Shane wrote:
> On Wed, Aug 10, 2005 at 04:24:51PM -0400, Sven Willenberger wrote:
> > On Wed, 2005-08-10 at 12:58 -0700, Shane wrote:
> > > On Wed, Aug 10, 2005 at 03:31:27PM -0400, Sven Willenberger wrote:
> > > > Right off the bat (if I am interpreting the results of your explain
> > > > analyze correctly) it looks like the planner is basing its decision to
> > > > seqscan as it thinks that it needs to filter over 1 million rows (versus
> > > > the 29,000 rows that actually are pulled). Perhaps increasing stats on
> > > > msgtime and then analyzing the table may help. Depending on your
> > > > hardware, decreasing random_page_cost in your postgresql.conf just a
> > > > touch may help too.
> >
> > Try increasing stats to 100 on just the msgtime column, not the default
> > (changing the default will only have an effect on newly created columns
> > -- you may want to change the default back to 10):
>
> Hi,
>
> I brought the statistics on msgtime up to 100, vacuum
> analyzed and brought random_page_cost down to 2.
> Unfortunately, explain analyze still wants to seqscan and
> estimates 1m returned rows.
>
> Is there a way to simply force an index usage for this
> particular query?
>
> S
>
What version of PostgreSQL are you running? Also, what happens if you
explain analyze choosing where msgtime > cast(now() - interval '6
months' as timestamp(0) without time zone); (instead of less than).
Depending on how you are connecting to run this query (script, webpage,
psql) you could always set enable_seq_scan=off; select ....; set
enable_seq_scan=on; scriptomagically.

Sven