From: valgog <valgog@gmail.com>
Subject: multicolumn index column order
Date: Mon, 23 Jul 2007 16:47:03 -0000
Organization: http://groups.google.com
Lines: 30
Message-ID: <1185209223.224236.163350@57g2000hsv.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
User-Agent: G2/1.0
Complaints-To: groups-abuse@google.com
Injection-Info: 57g2000hsv.googlegroups.com; posting-host=62.220.8.122;
	posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
To: pgsql-performance@postgresql.org

Hello all,

how to build an multicolumn index with one column order ASCENDING and
another column order DESCENDING?

The use case that I have is that I use 2 column index where the first
column is kind of flag and the second column is an actual ordering
column. The flag should be always ordered DESCENDING, but the second
column is ordered DESCENDING when it is a numeric column, and
ASCENDING when it is a text column.

CREATE TABLE storage (id int, flag int, numeric_data int, text_data
text);

SELECT * FROM storage
 ORDER BY flag DESC, numeric_column DESC
LIMIT 20 OFFSET 0;

SELECT * FROM storage
 ORDER BY flag DESC, text_column ASC
LIMIT 20 OFFSET 0;

Definitely the multicolumn index on (flag, numeric_column) is being
used.

But how to create an index on (flag, text_column DESC)?

I will try to index by ((-flag), text_column) and sort by (-flag) ASC,
but it, to say the truth, does not really look like a nice solution.