Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 5805C475461 for ; Fri, 22 Feb 2002 19:11:54 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1N0BlS21704; Fri, 22 Feb 2002 19:11:47 -0500 (EST) To: Oliver Elphick Cc: "Samuel J. Sutjiono" , pgsql-sql@postgresql.org Subject: Re: How does Index Scan get used 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> Comments: In-reply-to Oliver Elphick message dated "22 Feb 2002 19:32:53 +0000" Date: Fri, 22 Feb 2002 19:11:47 -0500 Message-ID: <21701.1014423107@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200202/331 X-Sequence-Number: 6753 Oliver Elphick 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