Re: optimizer tuning/forcing correct index use - Mailing list pgsql-sql

From Kelly Burkhart
Subject Re: optimizer tuning/forcing correct index use
Date
Msg-id 02031911121009.00735@krbdev
Whole thread Raw
In response to optimizer tuning/forcing correct index use  (Kelly Burkhart <kelly@tradebotsystems.com>)
Responses Re: optimizer tuning/forcing correct index use
List pgsql-sql
On Tuesday 19 March 2002 09:28 am, Tom Lane wrote:
> Kelly Burkhart <kelly@tradebotsystems.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

pgsql-sql by date:

Previous
From: Jan Wieck
Date:
Subject: Re: create function problem
Next
From: Tom Lane
Date:
Subject: Re: optimizer tuning/forcing correct index use