Re: [SQL] index on more-than-one columns?

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: s-fery(at)kkt(dot)sote(dot)hu
> Cc: PostgreSQL list <pgsql-sql(at)postgreSQL(dot)org>
> Subject: Re: [SQL] index on more-than-one columns?
>
> Engard Ferenc <fery(at)pons(dot)sote(dot)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
>

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83