Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by hub.org (8.9.3/8.9.3) with ESMTP id BAA82452 for ; Wed, 18 Aug 1999 01:06:56 -0400 (EDT) (envelope-from oleg@sai.msu.su) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.9.1/8.9.1) with SMTP id JAA02558; Wed, 18 Aug 1999 09:06:21 +0400 (MSD) Date: Wed, 18 Aug 1999 09:06:21 +0400 (MSD) From: Oleg Bartunov X-Sender: megera@ra To: Tom Lane cc: s-fery@kkt.sote.hu, PostgreSQL list Subject: Re: [SQL] index on more-than-one columns? In-Reply-To: <23231.934923869@sss.pgh.pa.us> Message-ID: Organization: Sternberg Astronomical Institute (Moscow University) MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Tom, does this right for all kind of indices ? For example, it's probably faster to do find for 2 indices separately in case of log(N) comlexity for btree-indices. Oleg On Tue, 17 Aug 1999, Tom Lane wrote: > Date: Tue, 17 Aug 1999 17:04:29 -0400 > From: Tom Lane > To: s-fery@kkt.sote.hu > Cc: PostgreSQL list > Subject: Re: [SQL] index on more-than-one columns? > > Engard Ferenc writes: > > Is there any advantage to use index on more columns against to use > > more separate indexes, e.g. creating index on t1 (a,b) instead of > > index on t1 (a) plus index on t1 (b)? > > If those are your two choices then the two separate indexes are > definitely more flexible, since the index on (a,b) can't be used > as a standalone index on b for queries that only involve b. > > In general I'd say that a multicolumn index is a pretty specialized > beast, and probably only worth its keep if you frequently do sorts with > that order and/or multikey joins against another table with a similar > index. > > For example: let's say you are trying to optimize for queries like > > SELECT ... FROM a, b WHERE a.f1 = b.f1 AND a.f2 = b.f2; > > If you have indexes on (f1,f2) for both tables then a two-column > mergejoin can be used --- that is, we scan both tables in the order > of the indexes. (BTW, 6.5 only manages to do this if both columns are > the same data type, but that will be fixed in 6.6.) Although that > looks cool, it's probably not a huge win compared to the one-column > merge you could do with indexes on either f1 or f2. It'd only be a > big win if neither f1 or f2 have a lot of distinct values by themselves > but f1 * f2 does. > > Also, although an index on (f1,f2) can be used as an index on f1 > alone, it's going to be bigger and therefore slower to access than > the single-column index. So, just adding on columns that you "might > need some of the time" isn't a win. > > Finally, remember that every index costs you time whenever the table > is modified, because the index has to be updated too. So it's not > a win to make a whole lot of indexes without specific reasons for > each one, unless the table is queried a *lot* more often than it > is modified. > > I'd probably suggest making one or two indexes on the individual > columns that are used most frequently in WHERE conditions, and not > going beyond that except when you have a specific frequently-used > query that you need to optimize. > > regards, tom lane > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83