Thread: index: specify sort order?
hi list
in ms-sql it is possible to specify the default order for an index (i.e. asc/desc). is this possible in pgsql as well, and if yes, how? i have some timestamp indices that i only use to sort the entries in descending sortorder...
thanks,
thomas
On Sat, 27 Aug 2005 me@alternize.com wrote: > in ms-sql it is possible to specify the default order for an index (i.e. > asc/desc). is this possible in pgsql as well, and if yes, how? i have > some timestamp indices that i only use to sort the entries in descending > sortorder... Well, if it's a single column index and sort we should be able to use the index backwards to do the sort (Explain would show Index Scan Backwards). Can you give more information on what precisely you're doing?
hi stephan yes, analyze is showing "index scan backwards". so for pg-sql it does not make a difference in speed whether "index scan" is done forward/backward? cool! in mssql it did make quite a difference to specify the prefered index sort order, hence my question. before i annoy the list with other migration-related, obvious questions, is there a good documentation on migrating from mssql to pgsql? cheers, thomas ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone.bigpanda.com> To: <me@alternize.com> Cc: <pgsql-novice@postgresql.org> Sent: Saturday, August 27, 2005 5:52 PM Subject: Re: [NOVICE] index: specify sort order? > On Sat, 27 Aug 2005 me@alternize.com wrote: > >> in ms-sql it is possible to specify the default order for an index (i.e. >> asc/desc). is this possible in pgsql as well, and if yes, how? i have >> some timestamp indices that i only use to sort the entries in descending >> sortorder... > > Well, if it's a single column index and sort we should be able to use the > index backwards to do the sort (Explain would show Index Scan Backwards). > Can you give more information on what precisely you're doing? > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
On Sat, 27 Aug 2005 me@alternize.com wrote: > yes, analyze is showing "index scan backwards". so for pg-sql it does not > make a difference in speed whether "index scan" is done forward/backward? > cool! in mssql it did make quite a difference to specify the prefered index > sort order, hence my question. Well, there is a case in PostgreSQL where it matters, which is in multi-column indexes where you want different directions for different columns (like col1 asc, col2 desc, col3 asc). If you need this, you can currently simulate it with special operator classes (searching the archives for reverse opclass or reverse operator class should give more information). > before i annoy the list with other migration-related, obvious questions, is > there a good documentation on migrating from mssql to pgsql? I don't know of one off hand (although there might be something on techdocs.postgresql.org), but hopefully someone else will chime in if they know of something specific.