Received: from gizmo.dimension.net (gizmo.dimension.net [209.12.7.20]) by hub.org (8.8.8/8.7.5) with ESMTP id OAA23170 for ; Thu, 9 Jul 1998 14:05:34 -0400 (EDT) Received: (from jaitken@localhost) by gizmo.dimension.net (8.8.8/8.8.8) id OAA03845; Thu, 9 Jul 1998 14:04:02 -0400 (EDT) From: Jeff Aitken Message-Id: <199807091804.OAA03845@gizmo.dimension.net> Subject: Re: [SQL] Single vs. multiple indexes In-Reply-To: <199807091716.NAA28651@candle.pha.pa.us> from Bruce Momjian at "Jul 9, 98 01:16:27 pm" To: maillist@candle.pha.pa.us (Bruce Momjian) Date: Thu, 9 Jul 1998 14:04:00 -0400 (EDT) Cc: tgl@sss.pgh.pa.us, pgsql-sql@postgreSQL.org Reply-to: jaitken@dimension.net X-Mailer: ELM [version 2.4ME+ PL38 (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? --Jeff