Thread: multicolumn index column order
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.
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
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 :-)
the manual somewhere states "... if archiving is enabled..." To me this implies that archiving can be disabled. However I cannot find the parameter to use to get this result. Or should I enable archiving and use a backup script like #!/usr/bin/bash exit 0 Would appreciate a hint. And yes I know I put my database in danger etc. This is for some benchmarks where I do not want the overhead of archiving. Jus a file system that will not fill with zillions of these 16MB WAL files ;^) Thanks Paul.
Paul van den Bogaard wrote: > the manual somewhere states "... if archiving is enabled..." To me this > implies that archiving can be disabled. However I cannot find the parameter > to use to get this result. Archiving is disabled by not setting archive_command. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
am Mon, dem 23.07.2007, um 19:24:48 +0200 mailte Paul van den Bogaard folgendes: > the manual somewhere states "... if archiving is enabled..." To me Please don't hijack other threads... (don't edit a mail-subject to create a new thread. Create a NEW mail!) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Perhaps you should've read the configuration-manual-page more carefully. ;) Besides, WAL-archiving is turned off by default, so if you see them being archived you actually enabled it earlier The "archive_command" is empty by default: "If this is an empty string (the default), WAL archiving is disabled." http://www.postgresql.org/docs/8.2/interactive/runtime-config-wal.html Best regards, Arjen On 23-7-2007 19:24 Paul van den Bogaard wrote: > the manual somewhere states "... if archiving is enabled..." To me this > implies that archiving can be disabled. However I cannot find the > parameter to use to get this result. Or should I enable archiving and > use a backup script like > > #!/usr/bin/bash > exit 0 > > > > Would appreciate a hint. And yes I know I put my database in danger etc. > This is for some benchmarks where I do not want the overhead of > archiving. Jus a file system that will not fill with zillions of these > 16MB WAL files ;^) > > Thanks > Paul. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Alvaro, thanks for the quick reply. Just to make sure: I do not set this command. This results in the database cycling through a finite set (hopefully small) set of WAL files. So old WAL files are reused once the engine thinks this can be done. Thanks Paul On 23-jul-2007, at 19:34, Alvaro Herrera wrote: > Paul van den Bogaard wrote: >> the manual somewhere states "... if archiving is enabled..." To me >> this >> implies that archiving can be disabled. However I cannot find the >> parameter >> to use to get this result. > > Archiving is disabled by not setting archive_command. > > -- > Alvaro Herrera http:// > www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. ------------------------------------------------------------------------ --------------------- Paul van den Bogaard Paul.vandenBogaard@sun.com CIE -- Collaboration and ISV Engineering, Opensource Engineering group Sun Microsystems, Inc phone: +31 334 515 918 Saturnus 1 extentsion: x (70)15918 3824 ME Amersfoort mobile: +31 651 913 354 The Netherlands fax: +31 334 515 001
On Jul 23, 7:24 pm, Paul.Vandenboga...@Sun.COM (Paul van den Bogaard) wrote: > the manual somewhere states "... if archiving is enabled..." To me > this implies that archiving can be disabled. However I cannot find > the parameter to use to get this result. Or should I enable archiving > and use a backup script like > > #!/usr/bin/bash > exit 0 > > Would appreciate a hint. And yes I know I put my database in danger > etc. This is for some benchmarks where I do not want the overhead of > archiving. Jus a file system that will not fill with zillions of > these 16MB WAL files ;^) > > Thanks > Paul. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majord...@postgresql.org so that your > message can get through to the mailing list cleanly Is it normal to spoil other threads? or is it a bug? If it is not a bug, please change the subject of the topic back to what it was! With best regards, Valentine Gogichashvili
On 7/24/07, andrew@pillette.com <andrew@pillette.com> wrote:
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...
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...
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.
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
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