Thread: Fwd: Index on table when using DESC clause
Begin forwarded message: <excerpt><bold><color><param>0000,0000,0000</param>From: </color></bold>Yves Vindevogel <<yves.vindevogel@implements.be> <bold><color><param>0000,0000,0000</param>Date: </color></bold>Mon 23 May 2005 19:23:16 CEST <bold><color><param>0000,0000,0000</param>To: </color></bold>pgsql-performance-owner@postgresql.org <bold><color><param>0000,0000,0000</param>Subject: </color>Index on table when using DESC clause </bold> Hi, I have a table with multiple fields. Two of them are documentname and pages I have indexes on documentname and on pages, and one extra on documentname + pages However, when I query my db using for instance order by pages, documentname, it is very fast. If I use order by pages desc, documentname, it is not fast at all, like it is not using the index properly at all. How can I avoid this ? Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller></excerpt> Begin forwarded message: > From: Yves Vindevogel <yves.vindevogel@implements.be> > Date: Mon 23 May 2005 19:23:16 CEST > To: pgsql-performance-owner@postgresql.org > Subject: Index on table when using DESC clause > > Hi, > > I have a table with multiple fields. Two of them are documentname and > pages > I have indexes on documentname and on pages, and one extra on > documentname + pages > > However, when I query my db using for instance order by pages, > documentname, it is very fast. > If I use order by pages desc, documentname, it is not fast at all, > like it is not using the index properly at all. > > How can I avoid this ? > > Met vriendelijke groeten, > Bien à vous, > Kind regards, > > Yves Vindevogel > Implements > <excerpt><smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be <italic><x-tad-smaller> First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. </x-tad-smaller></italic></smaller></excerpt>Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller>> > > Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 > > Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 > > Web: http://www.implements.be > > First they ignore you. Then they laugh at you. Then they fight you. > Then you win. > Mahatma Ghandi. Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements <smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be <italic><x-tad-smaller> First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.</x-tad-smaller></italic></smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.
Attachment
On Mon, May 23, 2005 at 07:41:19PM +0200, Yves Vindevogel wrote: > However, when I query my db using for instance order by pages, > documentname, it is very fast. > If I use order by pages desc, documentname, it is not fast at > all, like it is not using the index properly at all. Make an index on "pages desc, documentname asc". /* Steinar */ -- Homepage: http://www.sesse.net/
I tried that, but create index ixTest on table1 (pages desc, documentname) gives me a syntax error On 23 May 2005, at 20:03, Steinar H. Gunderson wrote: <excerpt>On Mon, May 23, 2005 at 07:41:19PM +0200, Yves Vindevogel wrote: <excerpt>However, when I query my db using for instance order by pages, documentname, it is very fast. If I use order by pages desc, documentname, it is not fast at all, like it is not using the index properly at all. </excerpt> Make an index on "pages desc, documentname asc". /* Steinar */ -- Homepage: http://www.sesse.net/ ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match </excerpt>Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller>I tried that, but create index ixTest on table1 (pages desc, documentname) gives me a syntax error On 23 May 2005, at 20:03, Steinar H. Gunderson wrote: > On Mon, May 23, 2005 at 07:41:19PM +0200, Yves Vindevogel wrote: >> However, when I query my db using for instance order by pages, >> documentname, it is very fast. >> If I use order by pages desc, documentname, it is not fast at >> all, like it is not using the index properly at all. > > Make an index on "pages desc, documentname asc". > > /* Steinar */ > -- > Homepage: http://www.sesse.net/ > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match > > Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements <smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be <italic><x-tad-smaller> First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.</x-tad-smaller></italic></smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.
Attachment
You didn't say what version of PostgreSQL you're trying. I recall old version doesn't used index for backward pagination. Oleg On Mon, 23 May 2005, Yves Vindevogel wrote: > I tried that, but > > create index ixTest on table1 (pages desc, documentname) > > gives me a syntax error > > > On 23 May 2005, at 20:03, Steinar H. Gunderson wrote: > >> On Mon, May 23, 2005 at 07:41:19PM +0200, Yves Vindevogel wrote: >>> However, when I query my db using for instance order by pages, >>> documentname, it is very fast. >>> If I use order by pages desc, documentname, it is not fast at >>> all, like it is not using the index properly at all. >> >> Make an index on "pages desc, documentname asc". >> >> /* Steinar */ >> -- >> Homepage: http://www.sesse.net/ >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: the planner will ignore your desire to choose an index scan if your >> joining column's datatypes do not match >> >> > Met vriendelijke groeten, > Bien ? vous, > Kind regards, > > Yves Vindevogel > Implements > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
As far as I know, to use a straight index Postgres requires either ORDER BY pages, description -- or -- ORDER BY pages DESC, description DESC. If you want the results by pages DESC, description ASC, then you have to make an index on an expression or define your own operator or something esoteric like that. I would think the ability to have an index where the columns don't all collate in the same direction would be an easy feature to add.
Attachment
God I love the sheer brilliance of that minus trick :-)) Tnx a lot BTW: Are there any plans to change this kind of indexing behaviour ? It makes no sense at all, and, it makes databases slow when you don't know about this. On 23 May 2005, at 23:15, Andrew Lazarus wrote: <excerpt>What you are trying to do makes perfect sense, but for some strange reason, Postgres doesn't like to do it. In a PG index, all of the columns are always stored in ascending order. So if you have an ORDER BY that is all ASC, it can start from the start of the index. And if you have an ORDER BY that is all DESC, it can start from the end. But if you want one column (like pages) DESC and the other (description) ASC, then PG will use a sequential scan or something else slow and stupid. Other RDBMS know how to do this, by supporting the CREATE INDEX foo ON bar(baz DESC, baz2 ASC) syntax. For PG, you need to fool it with an index on an expression, or a custom operator, or something. I once just made an extra column and used a trigger to be sure that -myvariable was in it at all times (-pages for you) and then made my index on the extra column. Since the extra column in ASC order is the same as the original in DESC, it works. <<andrew.vcf> </excerpt>Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller>God I love the sheer brilliance of that minus trick :-)) Tnx a lot BTW: Are there any plans to change this kind of indexing behaviour ? It makes no sense at all, and, it makes databases slow when you don't know about this. On 23 May 2005, at 23:15, Andrew Lazarus wrote: > What you are trying to do makes perfect sense, but for some strange > reason, Postgres doesn't like to do it. In a PG index, all of the > columns are always stored in ascending order. So if you have an ORDER > BY that is all ASC, it can start from the start of the index. And if > you have an ORDER BY that is all DESC, it can start from the end. But > if you want one column (like pages) DESC and the other (description) > ASC, then PG will use a sequential scan or something else slow and > stupid. > > Other RDBMS know how to do this, by supporting the > > CREATE INDEX foo ON bar(baz DESC, baz2 ASC) > > syntax. For PG, you need to fool it with an index on an expression, or > a custom operator, or something. I once just made an extra column and > used a trigger to be sure that -myvariable was in it at all times > (-pages for you) and then made my index on the extra column. Since the > extra column in ASC order is the same as the original in DESC, it > works. > <andrew.vcf> Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements <smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be <italic><x-tad-smaller> First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.</x-tad-smaller></italic></smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.