From: Tom Lane <tgl@sss.pgh.pa.us>
To: Oliver Elphick <olly@lfix.co.uk>
Cc: Samuel J. Sutjiono <ssutjiono@wc-group.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How does Index Scan get used
Date: Fri, 22 Feb 2002 19:11:47 -0500
Message-ID: <21701.1014423107@sss.pgh.pa.us> (raw)
In-Reply-To: <1014406374.13241.2406.camel@linda>
References: <018a01c1bbaf$befb84b0$110a010a@headquarters.wcgroup.com>
<1014401308.13240.2302.camel@linda>
<1014406374.13241.2406.camel@linda>
Oliver Elphick <olly@lfix.co.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, olly@lfix.co.uk, ssutjiono@wc-group.com
Subject: Re: How does Index Scan get used
In-Reply-To: <21701.1014423107@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox