Re: Queries not using Index

From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Daryl Herzmann" <akrherz(at)iastate(dot)edu>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Queries not using Index
Date: 2002-07-24 02:09:17
Message-ID: GNELIHDDFBOCMGBFGEFOKEEPCDAA.chriskl@familyhealth.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Have you tried playing with the statistics gatherer?

>From the ANALYZE docs:

"The extent of analysis can be controlled by adjusting the per-column
statistics target with ALTER TABLE ALTER COLUMN SET STATISTICS (see ALTER
TABLE). The target value sets the maximum number of entries in the
most-common-value list and the maximum number of bins in the histogram. The
default target value is 10, but this can be adjusted up or down to trade off
accuracy of planner estimates against the time taken for ANALYZE and the
amount of space occupied in pg_statistic. In particular, setting the
statistics target to zero disables collection of statistics for that column.
It may be useful to do that for columns that are never used as part of the
WHERE, GROUP BY, or ORDER BY clauses of queries, since the planner will have
no use for statistics on such columns. "

Just a thought...

Also, what is the result of:

select indexdef from pg_indexes where indexname='t2002_06_station_idx';

> Any thoughts? I am sorry to be causing all this trouble. I just want my
> queries to voom-voom!! Interestingly enough, I see that the SEQ SCAN is
> now estimated at 1730.63, when I first posted to this list, it
> was 3900.00
> or so. Errrr

It's no trouble. Cases where the planner fails are essential to improving
the planner. Ideally this query should use your index automatically...

Chris

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Daryl Herzmann 2002-07-24 02:24:01 Re: Queries not using Index
Previous Message Daryl Herzmann 2002-07-24 01:57:15 Re: Queries not using Index