Received: from candle.pha.pa.us (maillist@s5-03.ppp.op.net [209.152.195.67]) by hub.org (8.8.8/8.7.5) with ESMTP id OAA24790 for ; Thu, 9 Jul 1998 14:14:27 -0400 (EDT) Received: (from maillist@localhost) by candle.pha.pa.us (8.8.5/8.8.5) id OAA00116; Thu, 9 Jul 1998 14:13:30 -0400 (EDT) From: Bruce Momjian Message-Id: <199807091813.OAA00116@candle.pha.pa.us> Subject: Re: [SQL] Single vs. multiple indexes In-Reply-To: <199807091804.OAA03845@gizmo.dimension.net> from Jeff Aitken at "Jul 9, 1998 2: 4: 0 pm" To: jaitken@dimension.net Date: Thu, 9 Jul 1998 14:13:29 -0400 (EDT) Cc: tgl@sss.pgh.pa.us, pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL43 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > Bruce Momjian writes: > > Good question. The optimizer can only use only one index in a query, so > > if you create five indexes, the optimizer will pick the best one to use, > > and disregard the rest. If you create one index with five fields, it > > will use as much of the index as it can. If you restrict on the first > > field of the index, it will use only that part of the index. If you > > restrict on the first three fields, it will use all three parts, > > resulting in better performance than just having an index on the first > > field. > > > > If you restrict only on the second field of the index, the index is > > useless and will not be used. > > > > Just to be sure I understand what you're saying here, let's assume > I've got a table with three integer fields a, b, and c. I can do > one of two things: > > 1. Create an index on (a), an index on (b), and an index on (c). > 2. Create an index on (a, b, c). > > Now let's assume I perform a query that references b only. In this > case, if I understand you correctly, method #1 above is better, > because the index in method #2 will not be used. > > However, if I perform a query that references a and b, then method > #2 would be better, because the composite index will be used, > whereas in method #1, only one of the two relevant indices would be > chosen? Exactly. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)