473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 2719
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: robert | last post by:
my colleagues are convinced that having more than an index or two destroys performance on their batch runs. could be. i don't have the data, and i'm wondering whether there might be some...
16
by: romicva | last post by:
I've recently upgraded from 8.1.4 to 8.2.3. System slowed down noticably. Explain shows that indexes are not used. I did reorgs on indexes and tables, collected stats on key fields with detail...
0
by: Jernej Kos | last post 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...
5
by: Thomas F.O'Connell | last post by:
We've got a table that has a definition as follows: CREATE TABLE linking_table ( fk int8 REFERENCES source_table( pk1 ), value int8, PRIMARY KEY( fk1, value ) ); I would've thought that...
1
by: Thomas Yagel | last post by:
I have a situtation where a particular table includes a timestamp column and a id column. The query I am working with right now filters based on timestamp and orders based on ID. I have not...
4
by: sql_server_user | last post by:
Hi, I have a history table with about 400 million rows, with a unique composite nonclustered index on two columns (object id and time period) which is used for most of the queries into the...
2
by: Lyle Fairfield | last post by:
'Property Clustered As Boolean 'Member of DAO.Index Private Sub IsThereaClusteredIndex() Dim tdf As DAO.TableDef Dim idx As DAO.Index For Each tdf In DBEngine(0)(0).TableDefs For Each idx In...
0
by: loken0673 | last post by:
Hello All I want to Split multirow resultset to single row, multicolumn resultset my table is ProfileDetail(ProfileId bigint, PropertyId bigint PropertyValue nvarchar(400)) Table data : ...
5
by: WRH | last post by:
Hello I want to have a multicolumn listbox. I never used one before so I looked at a Help example. I set the multicolumn property and the column width and tested with this example... ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.