Re: Index usage in order by with multiple columns in order-by-clause

On Fri, Aug 10, 2007 at 04:53:12PM +0200, Andreas Joseph Krogh wrote:
> I have the following test-case:
>
> CREATE TABLE test(
> name varchar PRIMARY KEY,
> value varchar NOT NULL,
> created timestamp not null
> );
>
> create index test_lowernamevalue_idx ON test ((lower(name) || lower(value)));
> create index test_lowernamevaluecreated_idx ON test ((lower(name) ||
> lower(value)), created);
> andreak=# EXPLAIN ANALYZE select * from test order by lower(name) ||
> lower(value) ASC, created DESC;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------
> Sort (cost=60.39..62.32 rows=770 width=72) (actual time=0.034..0.034 rows=0
> loops=1)
> Sort Key: (lower((name)::text) || lower((value)::text)), created
> -> Seq Scan on test (cost=0.00..23.47 rows=770 width=72) (actual
> time=0.004..0.004 rows=0 loops=1)
> Total runtime: 0.123 ms
> (4 rows)
> In my application I often have a need to sort by more than 3 columns, so I'm
> really wondering if there is a way to make sorting of multiple columsn (each
> which may have different sort-order) use an index? Preferrably without having
> to create 2^N indexes.

first of all - you can try with separate indexes on lower()||lower(),
and created.

then - you can use a trick.
create a function that will reverse order of your date (using a simple
"-" operator)
and then index your lower() and output of this function.

you will need to modify the query, but it's perfectly doable.

for example:
create function test_ts(timestamp) returns interval as $BODY$
begin
return '2000-01-01 00:00:00'::timestamp-$1;
end;
$BODY$ language plpgsql immutable;

of course this particular date is irrelevant, we just have to substract
from something.

then:
create index test_lowernamevaluecreated_idx2 ON test ((lower(name) ||
lower(value)), test_ts(created));

and change your query to:
select * from test order by lower(name) || lower(value) ASC, test_ts(created);
it would show you what you need.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)