469,111 Members | 1,934 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

Multicolumn indexes and ORDER BY

I have a multicolumn index on two columns. If i use the columns in ORDER BY
like this:
ORDER BY col1, col2;

The index is used. But, if one column is sorted DESC it is not used:
ORDER BY col1 DESC, col2;

How can i make this work ?

Regards,
Jernej Kos.
--
Kostko <ko****@jweb-network.net>
JWeb-Network

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
8 2504
On Wed, Jun 16, 2004 at 07:12:26AM +0200, Jernej Kos wrote:
I have a multicolumn index on two columns. If i use the columns in ORDER BY
like this:
ORDER BY col1, col2;

The index is used. But, if one column is sorted DESC it is not used:
ORDER BY col1 DESC, col2;

How can i make this work ?
Try:

ORDER BY col1 DESC, col2 desc;

Hope this helps,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAz+S9Y5Twig3Ge+YRAoXnAKCZkwNCy53zmaew5wAOEf z2IdSLRwCgm13Q
MXCHfq1UvlHRO/DcV5ATFPE=
=AoVp
-----END PGP SIGNATURE-----

Nov 23 '05 #2
On Wed, Jun 16, 2004 at 07:12:26AM +0200, Jernej Kos wrote:
I have a multicolumn index on two columns. If i use the columns in ORDER BY
like this:
ORDER BY col1, col2;

The index is used. But, if one column is sorted DESC it is not used:
ORDER BY col1 DESC, col2;

How can i make this work ?
Try:

ORDER BY col1 DESC, col2 desc;

Hope this helps,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAz+S9Y5Twig3Ge+YRAoXnAKCZkwNCy53zmaew5wAOEf z2IdSLRwCgm13Q
MXCHfq1UvlHRO/DcV5ATFPE=
=AoVp
-----END PGP SIGNATURE-----

Nov 23 '05 #3
Yes i tried that already - and as you said, it works. But i need to have one
column sorted DESC and one ASC. Is there any way this could be done ?

Regards,
Jernej Kos.

On Wednesday 16 of June 2004 08:12, Martijn van Oosterhout wrote:
On Wed, Jun 16, 2004 at 07:12:26AM +0200, Jernej Kos wrote:
I have a multicolumn index on two columns. If i use the columns in ORDER
BY like this:
ORDER BY col1, col2;

The index is used. But, if one column is sorted DESC it is not used:
ORDER BY col1 DESC, col2;

How can i make this work ?


Try:

ORDER BY col1 DESC, col2 desc;

Hope this helps,


--
Jernej Kos <ko****@jweb-network.net>
JWeb-Network

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4
Yes i tried that already - and as you said, it works. But i need to have one
column sorted DESC and one ASC. Is there any way this could be done ?

Regards,
Jernej Kos.

On Wednesday 16 of June 2004 08:12, Martijn van Oosterhout wrote:
On Wed, Jun 16, 2004 at 07:12:26AM +0200, Jernej Kos wrote:
I have a multicolumn index on two columns. If i use the columns in ORDER
BY like this:
ORDER BY col1, col2;

The index is used. But, if one column is sorted DESC it is not used:
ORDER BY col1 DESC, col2;

How can i make this work ?


Try:

ORDER BY col1 DESC, col2 desc;

Hope this helps,


--
Jernej Kos <ko****@jweb-network.net>
JWeb-Network

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #5
Jernej Kos <ko****@jweb-network.net> writes:
Yes i tried that already - and as you said, it works. But i need to have one
column sorted DESC and one ASC. Is there any way this could be done ?


Not easily. You could look into building a "reverse sort" operator
class for one index column or the other. There is discussion of how
to do this in the archives, but I don't know of anyone having actually
gotten off their duff and done it. For reasonable index performance
this would require writing at least one function in C (a pretty trivial
one, but nonetheless a C function).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #6
Jernej Kos <ko****@jweb-network.net> writes:
Yes i tried that already - and as you said, it works. But i need to have one
column sorted DESC and one ASC. Is there any way this could be done ?


Not easily. You could look into building a "reverse sort" operator
class for one index column or the other. There is discussion of how
to do this in the archives, but I don't know of anyone having actually
gotten off their duff and done it. For reasonable index performance
this would require writing at least one function in C (a pretty trivial
one, but nonetheless a C function).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7
Jernej Kos <ko****@jweb-network.net> writes:
Well, writing a C function is not a problem ;) So where could i find any
documentation regarding this matter ?


Read the "Interfacing Extensions To Indexes" docs chapter. A crude
example for integers would go like

regression=# create function revcmp(int,int) returns int as
regression-# 'select $2 - $1' language sql;
CREATE FUNCTION
regression=# create operator class rev_int_ops for type int using btree as
regression-# operator 1 > ,
regression-# operator 2 >= ,
regression-# operator 3 = ,
regression-# operator 4 <= ,
regression-# operator 5 < ,
regression-# function 1 revcmp(int,int);
CREATE OPERATOR CLASS

(compare the operator order here to the "standard" btree order shown in
the docs --- we're swapping < for > and <= for >=)

This actually works:

regression=# create table foo (f1 int, f2 int);
CREATE TABLE
regression=# create index fooi on foo (f1, f2 rev_int_ops);
CREATE INDEX
regression=# explain select * from foo order by f1, f2 desc;
QUERY PLAN
--------------------------------------------------------------------
Index Scan using fooi on foo (cost=0.00..52.00 rows=1000 width=8)
(1 row)

regression=# explain select * from foo order by f1 desc, f2 asc;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan Backward using fooi on foo (cost=0.00..52.00 rows=1000 width=8)
(1 row)

but index performance would be pretty sucky without reducing the
comparator function to C. Also I didn't consider overflow when writing
this comparator function, so the above would probably fall over if faced
with index entries outside +/- 1 billion or so.

At the C level it'd probably be best to call the standard comparator
function for the type and then negate its result, viz
PG_RETURN_INT32(- DatumGetInt32(btint4cmp(fcinfo)));
which reduces what might otherwise be a bit complicated to trivial
boilerplate.

We have previously discussed putting together a contrib package that
implements reverse-sort opclasses of this kind for all the standard
datatypes. If you feel like doing the legwork, the submission would
be gratefully accepted ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #8
Jernej Kos <ko****@jweb-network.net> writes:
Well, writing a C function is not a problem ;) So where could i find any
documentation regarding this matter ?


Read the "Interfacing Extensions To Indexes" docs chapter. A crude
example for integers would go like

regression=# create function revcmp(int,int) returns int as
regression-# 'select $2 - $1' language sql;
CREATE FUNCTION
regression=# create operator class rev_int_ops for type int using btree as
regression-# operator 1 > ,
regression-# operator 2 >= ,
regression-# operator 3 = ,
regression-# operator 4 <= ,
regression-# operator 5 < ,
regression-# function 1 revcmp(int,int);
CREATE OPERATOR CLASS

(compare the operator order here to the "standard" btree order shown in
the docs --- we're swapping < for > and <= for >=)

This actually works:

regression=# create table foo (f1 int, f2 int);
CREATE TABLE
regression=# create index fooi on foo (f1, f2 rev_int_ops);
CREATE INDEX
regression=# explain select * from foo order by f1, f2 desc;
QUERY PLAN
--------------------------------------------------------------------
Index Scan using fooi on foo (cost=0.00..52.00 rows=1000 width=8)
(1 row)

regression=# explain select * from foo order by f1 desc, f2 asc;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan Backward using fooi on foo (cost=0.00..52.00 rows=1000 width=8)
(1 row)

but index performance would be pretty sucky without reducing the
comparator function to C. Also I didn't consider overflow when writing
this comparator function, so the above would probably fall over if faced
with index entries outside +/- 1 billion or so.

At the C level it'd probably be best to call the standard comparator
function for the type and then negate its result, viz
PG_RETURN_INT32(- DatumGetInt32(btint4cmp(fcinfo)));
which reduces what might otherwise be a bit complicated to trivial
boilerplate.

We have previously discussed putting together a contrib package that
implements reverse-sort opclasses of this kind for all the standard
datatypes. If you feel like doing the legwork, the submission would
be gratefully accepted ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by romicva | last post: by
reply views Thread by Jernej Kos | last post: by
5 posts views Thread by Thomas F.O'Connell | last post: by
1 post views Thread by Thomas Yagel | last post: by
4 posts views Thread by sql_server_user | last post: by
2 posts views Thread by Lyle Fairfield | last post: by
5 posts views Thread by WRH | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.