Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35]) by postgresql.org (Postfix) with ESMTP id 4157F475481 for ; Sun, 3 Mar 2002 11:02:49 -0500 (EST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g23G2hJ07473; Sun, 3 Mar 2002 11:02:43 -0500 (EST) From: Bruce Momjian Message-Id: <200203031602.g23G2hJ07473@candle.pha.pa.us> Subject: Re: Index doesn't appear to be working. In-Reply-To: <20020302234538.Y58150-100000@megazone23.bigpanda.com> To: Stephan Szabo Date: Sun, 3 Mar 2002 11:02:43 -0500 (EST) Cc: John Oakes , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL96 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200203/25 X-Sequence-Number: 6907 > > Why isn't it using the index? This query returns about 5,600 of 10,000 > > records. It is faster to just do a seq scan because it returns such a high > > percentage or records? Thanks, I appreciate the help! > > Usually the seq scan will be faster in this kind of situation. Because > the system needs to load the matching rows anyway, it's likely to result > in reading all the blocks of the table and paying a penalty for seeking > around the file. We are getting this "no index use" question over and over again, and I was wondering why the FAQ item was not answering this question for people. It turns out the wording was not very clear and I have updated this FAQ item to more clearly answer the question. Seems I kept adding to that item without restructuring the information:

4.8) My queries are slow or don't make use of the indexes. Why?

Indexes are not automatically used by every query. Indexes are only used if the table is larger than a minimum size, and the index selects only a small percentage of the rows in the table. This is because the random disk access caused by an index scan is sometimes slower than a straight read through the table, or sequential scan.

To determine if an index should be used, PostgreSQL must have statistics about the table. These statistics are collected using VACUUM ANALYZE, or simply ANALYZE. Using statistics, the optimizer knows how many rows are in the table, and can better determine if indexes should be used. Statistics are also valuable in determining optimal join order and join methods. Statistics collection should be performed periodically as the contents of the table change.

Indexes are normally not used for ORDER BY or to perform joins. A sequential scan followed by an explicit sort is usually faster than an index scan of a large table.

However, LIMIT combined with ORDER BY often will use an index because only a small portion of the table is returned.

When using wild-card operators such as LIKE or ~, indexes can only be used if the beginning of the search is anchored to the start of the string. Therefore, to use indexes, LIKE patterns must not start with %, and ~(regular expression) patterns must start with ^.

-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026