On Tue, May 22, 2001 at 09:43:02PM +0200, Peter Eisentraut wrote:
> kavoos writes:
>
> > create index lower_title on titles (lower(title));
> > vacuum analyze;
> > ...
> > explain select * from titles where lower(title) = 'monde';
> > Seq Scan on titles (cost=0.00..39392.10 rows=14145 width=44)
> >
> > Why it does not use the index ?
>
> The planner estimates that this query returns 14145 rows. If this is more
> than a small fraction of the rows in this table then a sequential scan is
> better than an index scan.
>
> The questions are, how many rows does this query really return, and how
> many rows are in this table. Btw., this is discussed in the manual under
> performance tips.
In our database there are several cases where postgres incorrectly chooses a
sequential scan. The table in question is 250MB (1.2 million rows) so a
sequential scan takes a *long* time. The disk read speed is 13MB/s so it
takes around 20 seconds to scan the whole table.
Now the average number of rows per instance of the primary key is around 470
but it can go as high as 16,000.
But I know something that postgres doesn't. The data is clustered somewhat
around the id we're comparing on. There is a "runlength" involved. Thus,
when doing an index scan, once it has read in the first tuple of a run there
will be more just sitting in the cache at basically zero cost.
I wrote a program to get the distribution of runlengths for each field in
the table. What it found was that for only 0.6% or rows did that ID appear
alone. Over 95% of rows are in runs of 10 or more. 80% are 50 or more. This
means that Postgres vastly overestimates the cost of an index scan. That's
just one example. We have one column where 80% is 200 or more.
No, I'm not clustering the data intentionally. It's just a product of the
way our system processes data.
Currently I work around this by fiddling enable_seqscan is strategic places
but that's blunt instrument. It affects all tables, not just that one. It
occured to me to edit the values in pg_statistic directly as well.
If anyones interested I can post the program and the actual output.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
