Re: How does Index Scan get used

Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> On Fri, 2002-02-22 at 18:08, Oliver Elphick wrote:
>> PostgreSQL thinks there are only 10 rows in the table. Therefore it
>> will not choose an index scan because for so few rows it is bound to be
>> more expensive than a sequential scan.

> But then I saw Tom's mail saying that it does use the index. How is
> that? Surely these few values would all be brought in in one single
> disk read?

Well, there are two different issues here. One is whether the query
*can* use the index --- that depends on your locale, as I pointed out,
and also on the details of the LIKE or regex pattern being used, as
Frank Bax points out nearby. The other issue is whether the planner
*will choose to* use the index, when it has a choice.

Oliver is quite correct that the planner would not choose to use the
index on such a small table --- if it knows the table is small. But
in these dummy examples that we're discussing, we just created the table
and index and then put a few values in (the order is important BTW),
and we never vacuumed. In this scenario the table size estimates in
pg_class have never been changed from the initial dummy values that
CREATE TABLE puts in --- and those dummy values are deliberately set
large enough to allow index scans. (10 pages / 1000 rows, IIRC.)
We don't initialize the size estimates to zero, because if we did,
the planner would make totally foolish choices for never-vacuumed
tables ... which could actually contain lots of data.

VACUUM the test table, and it'll stop using the index, unless you put a
lot more test data in than we've used in these examples.

Oh BTW: the "10 rows" Oliver was wondering about are not the number of
rows the planner thinks are in the table; they're the estimated number
of result rows. Since there's no VACUUM ANALYZE stats available either,
this is just a default selectivity estimate for the match clause (0.01)
times the initial dummy reltuples value (1000). Not much content in it
at all, eh?

regards, tom lane