Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.8.8/8.7.5) with ESMTP id RAA16464 for ; Fri, 10 Jul 1998 17:27:40 -0400 (EDT) Received: by bjke.com; id QAA00470; Fri, 10 Jul 1998 16:25:44 -0500 (CDT) Received: from cpsmail.cpsgroup.com(144.210.12.11) by gauntlet.bjke.com via smap (4.1) id xmac00281; Fri, 10 Jul 98 16:22:42 -0500 Received: by cpsmail with Internet Mail Service (5.0.1458.49) id <34YSMYX1>; Fri, 10 Jul 1998 12:37:37 -0500 Message-ID: From: "Jackson, DeJuan" To: Bruce Momjian , jaitken@dimension.net Cc: tgl@sss.pgh.pa.us, pgsql-sql@postgreSQL.org Subject: RE: [SQL] Single vs. multiple indexes Date: Fri, 10 Jul 1998 12:37:35 -0500 X-Priority: 3 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.0.1458.49) Content-Type: text/plain > > 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. > But let's not forget one of the wonders of PostgreSQL. In this world you can have your cake and eat it to. So, create all 4 indices and let the optimizer sort em out for your queries. -DEJ