Skip site navigation (1) Skip section navigation (2)

multicolumn index column order

From: valgog <valgog(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: multicolumn index column order
Date: 2007-07-23 16:47:03
Message-ID: 1185209223.224236.163350@57g2000hsv.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.


Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-07-23 17:00:40
Subject: Re: multicolumn index column order
Previous:From: Tom LaneDate: 2007-07-23 16:35:28
Subject: Re: User concurrency thresholding: where do I look?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group