Postgres Pro
Downloads
Home   >   mailing lists

Thread: AW: [HACKERS] Re: Let's talk up 6.3

AW: [HACKERS] Re: Let's talk up 6.3

From
Zeugswetter Andreas SARZ
Date:
> Would a rule be that
> if the first attribute of an index is unique, then additional
> attributes are basically useless?

For PostgreSQL this is currently true, since indexes are currently not
used for order by. If you have a unique first column in an index,
then all following columns could only be used for sorting,
not for faster access (access actually gets worse).

Andreas

Re: AW: [HACKERS] Re: Let's talk up 6.3

From
Bruce Momjian
Date:
>
> > Would a rule be that
> > if the first attribute of an index is unique, then additional
> > attributes are basically useless?
>
> For PostgreSQL this is currently true, since indexes are currently not
> used for order by. If you have a unique first column in an index,
> then all following columns could only be used for sorting,
> not for faster access (access actually gets worse).

Sorry, don't follow this logic.  He is not restricting on the first
field of the index, so the index is not used.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Re: AW: [HACKERS] Re: Let's talk up 6.3

From
dg@illustra.com (David Gould)
Date:
Andreas:
> > Would a rule be that
> > if the first attribute of an index is unique, then additional
> > attributes are basically useless?
>
> For PostgreSQL this is currently true, since indexes are currently not
> used for order by. If you have a unique first column in an index,
> then all following columns could only be used for sorting,
> not for faster access (access actually gets worse).

The rule 'if the first attribute of an index is unique, then additional
attributes are basically useless' is exactly correct for all systems, not
just PostgreSQL. It has nothing to do with whether indexes are used for
'orderby'.

A bit of thought will reveal that if the first key is unique then there
is no way any subsequent key can influence the sort order. Consider:

 col1 col2
 ---- ----
  A    9
  B    5
  C    1
 ...  ...

There is no value you can put in col2 that will make 'A' sort after 'B'.

-dg

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
"Of course, someone who knows more about this will correct me if I'm wrong,
 and someone who knows less will correct me if I'm right."
               --David Palmer (palmer@tybalt.caltech.edu)