Lists: | pgsql-general |
---|
From: | Jernej Kos <kostko(at)jweb-network(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Multicolumn indexes and ORDER BY |
Date: | 2004-06-16 05:12:26 |
Message-ID: | 200406160712.26939.kostko@jweb-network.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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 <kostko(at)jweb-network(dot)net>
JWeb-Network
From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Jernej Kos <kostko(at)jweb-network(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Multicolumn indexes and ORDER BY |
Date: | 2004-06-16 06:12:13 |
Message-ID: | 20040616061213.GA3567@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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 <kleptog(at)svana(dot)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.
From: | Jernej Kos <kostko(at)jweb-network(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Multicolumn indexes and ORDER BY |
Date: | 2004-06-16 13:39:35 |
Message-ID: | 200406161539.35947.kostko@jweb-network.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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 <kostko(at)jweb-network(dot)net>
JWeb-Network
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jernej Kos <kostko(at)jweb-network(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Multicolumn indexes and ORDER BY |
Date: | 2004-06-16 14:32:35 |
Message-ID: | 21892.1087396355@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Jernej Kos <kostko(at)jweb-network(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jernej Kos <kostko(at)jweb-network(dot)net> |
Cc: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: Multicolumn indexes and ORDER BY |
Date: | 2004-06-16 15:06:43 |
Message-ID: | 22227.1087398403@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Jernej Kos <kostko(at)jweb-network(dot)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