Re: optimizer tuning/forcing correct index use

On Tuesday 19 March 2002 09:28 am, Tom Lane wrote:
> Kelly Burkhart <kelly(at)tradebotsystems(dot)com> writes:
> > The critical part is driving the fill table query from a sequential scan
> > rather than a scan of fill_ak2.
>
> Have you done an ANALYZE or VACUUM ANALYZE lately? If so, what do you
> get from
>
> select * from pg_stats where tablename = 'fill';
> select * from pg_class where relname = 'fill';
>
> Offhand I am guessing that the table is fairly well ordered by fill_ts
> and the planner is underestimating the effects of this. There is a
> provision in there to try to account for data ordering, but it's new
> code in 7.2 and doubtless still needs refinement.

I've attached the results of those queries.

The table was loaded in one week chunks, mostly but not entirely in order.
11/2001-3/2002 was loaded first in order, then 1/2001-10/2001 was loaded some
time later. Each dump file is probably in order, if you trust that SQL
Server used the index order in the query (which you probably can).

In the future, when/if this database becomes production, each week we will
nuke all rows older than 1 year (or maybe 2 years... who knows). So after a
point, the db should grow little.

-K