Date: Sat, 11 Aug 2007 21:05:22 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
To: Andreas Joseph Krogh <andreak@officenet.no>
Cc: pgsql-sql@postgresql.org
Subject: Re: Index usage in order by with multiple columns in order-by-clause
Message-ID: <20070811190521.GA7915@depesz.com>
Reply-To: depesz@depesz.com
References: <200708101653.13011.andreak@officenet.no>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <200708101653.13011.andreak@officenet.no>
User-Agent: Mutt/1.5.13 (2006-08-11)

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)