pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
index on more-than-one columns?
3+ messages / 3 participants
[nested] [flat]

* index on more-than-one columns?
@ 1999-08-14 14:51 Engard Ferenc <fery@pons.sote.hu>
  1999-08-17 21:04 ` Re: [SQL] index on more-than-one columns? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Engard Ferenc @ 1999-08-14 14:51 UTC (permalink / raw)
  To: pgsql-sql

Hi all,

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)? Let's suppose that the queries
include all sorts of combinations.

Thx:
Circum

 __  @
/  \    _   _                                           Engard Ferenc
l    | ( \ /  | | (\/)                      mailto:s-fery@kkt.sote.hu
\__/ | |   \_ \_/ I  I                    http://pons.sote.hu/~s-fery





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] index on more-than-one columns?
  1999-08-14 14:51 index on more-than-one columns? Engard Ferenc <fery@pons.sote.hu>
@ 1999-08-17 21:04 ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-08-18 05:06   ` Re: [SQL] index on more-than-one columns? Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 1999-08-17 21:04 UTC (permalink / raw)
  To: s-fery@kkt.sote.hu; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] index on more-than-one columns?
  1999-08-14 14:51 index on more-than-one columns? Engard Ferenc <fery@pons.sote.hu>
  1999-08-17 21:04 ` Re: [SQL] index on more-than-one columns? Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-08-18 05:06   ` Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 0 replies; 3+ messages in thread

From: Oleg Bartunov @ 1999-08-18 05:06 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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@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? 
> 
> 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
> 

_____________________________________________________________
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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-08-18 05:06 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-14 14:51 index on more-than-one columns? Engard Ferenc <fery@pons.sote.hu>
1999-08-17 21:04 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-18 05:06   ` Oleg Bartunov <oleg@sai.msu.su>

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