Re: index problem

On Mon, Jan 10, 2005 at 08:11:46PM +0100, Tomka Gergely wrote:

> tomka=> EXPLAIN SELECT ertek from meres where id=62;
>                           QUERY PLAN
> ---------------------------------------------------------------
>  Seq Scan on meres  (cost=0.00..462872.90 rows=220854 width=8)
>    Filter: (id = 62)
> (2 rows)
>
> In the table is approx 23 million lines.

Your table definition shows that id is a smallint.  In versions of
PostgreSQL prior to 8.0 you'll have to use a cast (WHERE id=62::smallint)
to make the planner consider using an index.  Note that I said
*consider* using an index -- if the planner thinks a sequential
scan will be faster then it'll use a sequential scan despite the
presence of an index.  The more rows the planner estimates the query
will return, the more likely it'll be to prefer a sequential scan.

> I want to use the indexes, really.

I think what you really mean is that you want your queries to be
as fast as possible.  For queries that return a significant fraction
of a table, a sequential scan will be faster than an index scan.

In your example, the planner estimates that the query will return
220854 rows, or about 1% of the total number of rows.  How accurate
is that estimate?  You can use EXPLAIN ANALYZE to compare the
estimate to the actual query results.  If the numbers are significantly
different then consider increasing the statistics on the column in
question and re-analyzing the table (see "Statistics Used by the
Planner" in the "Performance Tips" chapter of the documentation).

If the planner insists on using a sequential scan despite your
attempts to make it use an index, you can set the enable_seqscan
configuration variable to "off" to see if an index scan really would
be faster.  First run EXPLAIN ANALYZE on the query several times
with enable_seqscan set to "on", then set enable_seqscan to "off"
and run EXPLAIN ANALYZE several more times (the purpose of running
the query several times is to allow for disk caching -- the first
query might be orders of magnitude slower than those that follow
because the latter are taking advantage of cached data).  If the
index scan is significantly faster, then post the EXPLAIN ANALYZE
output to the list so we can take a closer look.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/