pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedmulticolumn index column order
7+ messages / 5 participants
[nested] [flat]
* multicolumn index column order
@ 2007-07-23 16:47 valgog <valgog@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: valgog @ 2007-07-23 16:47 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: multicolumn index column order
@ 2007-07-23 17:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: valgog <valgog@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-07-23 17:00 UTC (permalink / raw)
To: valgog <valgog@gmail.com>; +Cc: pgsql-performance
valgog <valgog@gmail.com> writes:
> how to build an multicolumn index with one column order ASCENDING and
> another column order DESCENDING?
Use 8.3 ;-)
In existing releases you could fake it with a custom reverse-sorting
operator class, but it's a pain in the neck to create one.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: multicolumn index column order
@ 2007-07-23 17:13 valgog <valgog@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: valgog @ 2007-07-23 17:13 UTC (permalink / raw)
To: pgsql-performance
On Jul 23, 7:00 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> valgog <val...@gmail.com> writes:
> > how to build an multicolumn index with one column order ASCENDING and
> > another column order DESCENDING?
>
> Use 8.3 ;-)
>
> In existing releases you could fake it with a custom reverse-sorting
> operator class, but it's a pain in the neck to create one.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
ok, thanks for a rapid answer, can live with the ((-flag),
text_column) functional multicolumn index by now.
Waiting for 8.3 :-)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: multicolumn index column order
@ 2007-07-24 07:10 andrew@pillette.com
0 siblings, 1 reply; 7+ messages in thread
From: andrew@pillette.com @ 2007-07-24 07:10 UTC (permalink / raw)
To: valgog <valgog@gmail.com>; +Cc: pgsql-performance
valgog <valgog@gmail.com> wrote ..
> On Jul 23, 7:00 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> > valgog <val...@gmail.com> writes:
> > > how to build an multicolumn index with one column order ASCENDING and
> > > another column order DESCENDING?
> >
> > Use 8.3 ;-)
> >
> > In existing releases you could fake it with a custom reverse-sorting
> > operator class, but it's a pain in the neck to create one.
I've often gotten what I want by using a calculated index on (f1, -f2). ORDER BY will take an expression, e.g. ORDER BY f1, -f2. Simpler than a custom operator.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: multicolumn index column order
@ 2007-07-24 07:40 Valentine Gogichashvili <valgog@gmail.com>
parent: andrew@pillette.com
0 siblings, 1 reply; 7+ messages in thread
From: Valentine Gogichashvili @ 2007-07-24 07:40 UTC (permalink / raw)
To: andrew@pillette.com <andrew@pillette.com>; +Cc: pgsql-performance
On 7/24/07, andrew@pillette.com <andrew@pillette.com> wrote:
>
> valgog <valgog@gmail.com> wrote ..
> > On Jul 23, 7:00 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> > > valgog <val...@gmail.com> writes:
> > > > how to build an multicolumn index with one column order ASCENDING
> and
> > > > another column order DESCENDING?
> > >
> > > Use 8.3 ;-)
> > >
> > > In existing releases you could fake it with a custom reverse-sorting
> > > operator class, but it's a pain in the neck to create one.
>
> I've often gotten what I want by using a calculated index on (f1, -f2).
> ORDER BY will take an expression, e.g. ORDER BY f1, -f2. Simpler than a
> custom operator.
>
Yes, this is true, but I do now know how to make text order be reversible?
There is no - (minus) operator for text value. By now it is not a problem
for me, but theoretically I do not see other chance to reverse text fields
order...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: multicolumn index column order
@ 2007-07-25 08:34 valgog <valgog@gmail.com>
parent: Valentine Gogichashvili <valgog@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: valgog @ 2007-07-25 08:34 UTC (permalink / raw)
To: pgsql-performance
On Jul 25, 2:14 am, Lew <l...@lewscanon.nospam> wrote:
>
> How about two indexes, one on each column? Then the indexes will cooperate
> when combined in a WHERE clause.
> <http://www.postgresql.org/docs/8.2/interactive/indexes-bitmap-scans.html;
>
> I don't believe the index makes a semantic difference with regard to ascending
> or descending. An index is used to locate records in the selection phase of a
> query or modification command.
>
> --
> Lew
Ordered indexes (b-tree in this case) are also used to get the needed
record order and it is absolutely not necessary to have a WHARE clause
in your select statement to use them when you are using ORDER BY.
--
Valentine
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: multicolumn index column order
@ 2007-07-27 20:03 Lew <lew@lewscanon.nospam>
parent: valgog <valgog@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Lew @ 2007-07-27 20:03 UTC (permalink / raw)
To: pgsql-performance
valgog wrote:
> On Jul 25, 2:14 am, Lew <l...@lewscanon.nospam> wrote:
>> How about two indexes, one on each column? Then the indexes will cooperate
>> when combined in a WHERE clause.
>> <http://www.postgresql.org/docs/8.2/interactive/indexes-bitmap-scans.html;
>>
>> I don't believe the index makes a semantic difference with regard to ascending
>> or descending. An index is used to locate records in the selection phase of a
>> query or modification command.
>>
>> --
>> Lew
>
> Ordered indexes (b-tree in this case) are also used to get the needed
> record order and it is absolutely not necessary to have a WHARE clause
> in your select statement to use them when you are using ORDER BY.
But does that affect anything when you "ORDER BY foo ASC" vs. when you "ORDER
BY foo DESC"?
For use by ORDER BY, separate column indexes are an even better idea.
--
Lew
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-07-27 20:03 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-23 16:47 multicolumn index column order valgog <valgog@gmail.com>
2007-07-23 17:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-23 17:13 ` valgog <valgog@gmail.com>
2007-07-24 07:10 Re: multicolumn index column order andrew@pillette.com
2007-07-24 07:40 ` Valentine Gogichashvili <valgog@gmail.com>
2007-07-25 08:34 ` valgog <valgog@gmail.com>
2007-07-27 20:03 ` Lew <lew@lewscanon.nospam>
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