pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: s-fery@kkt.sote.hu
Cc: PostgreSQL list <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] index on more-than-one columns?
Date: Tue, 17 Aug 1999 17:04:29 -0400
Message-ID: <23231.934923869@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.02.9908141646590.312-100000@domesticus.sote.hu>

Engard Ferenc <fery@pons.sote.hu> 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



view thread (3+ messages)  latest in thread

Message-ID: <23231.934923869@sss.pgh.pa.us>
Permalink:  ../23231.934923869@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/23231.934923869@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, s-fery@kkt.sote.hu
  Subject: Re: [SQL] index on more-than-one columns?
  In-Reply-To: <23231.934923869@sss.pgh.pa.us>
  References: <Pine.LNX.4.02.9908141646590.312-100000@domesticus.sote.hu> <23231.934923869@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox