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) ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
