From: valgog <valgog@gmail.com>
To: pgsql-performance@postgresql.org
Subject: multicolumn index column order
Date: Mon, 23 Jul 2007 16:47:03 -0000
Message-ID: <1185209223.224236.163350@57g2000hsv.googlegroups.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: valgog@gmail.com
Subject: Re: multicolumn index column order
In-Reply-To: <1185209223.224236.163350@57g2000hsv.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox