pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFwd: Index on table when using DESC clause
6+ messages / 4 participants
[nested] [flat]
* Fwd: Index on table when using DESC clause
@ 2005-05-23 17:41 Yves Vindevogel <yves.vindevogel@implements.be>
2005-05-23 18:03 ` Re: Fwd: Index on table when using DESC clause Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-05-23 20:27 ` Re: Fwd: Index on table when using DESC clause Andrew Lazarus <andrew@pillette.com>
0 siblings, 2 replies; 6+ messages in thread
From: Yves Vindevogel @ 2005-05-23 17:41 UTC (permalink / raw)
To: pgsql-performance
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
>
>
>
> 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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Fwd: Index on table when using DESC clause
2005-05-23 17:41 Fwd: Index on table when using DESC clause Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-05-23 18:03 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-05-23 19:02 ` Re: Fwd: Index on table when using DESC clause Yves Vindevogel <yves.vindevogel@implements.be>
1 sibling, 1 reply; 6+ messages in thread
From: Steinar H. Gunderson @ 2005-05-23 18:03 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: Index on table when using DESC clause
2005-05-23 17:41 Fwd: Index on table when using DESC clause Yves Vindevogel <yves.vindevogel@implements.be>
2005-05-23 18:03 ` Re: Fwd: Index on table when using DESC clause Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2005-05-23 19:02 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-05-23 19:46 ` Re: Fwd: Index on table when using DESC clause Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 6+ messages in thread
From: Yves Vindevogel @ 2005-05-23 19:02 UTC (permalink / raw)
To: pgsql-performance
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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Fwd: Index on table when using DESC clause
2005-05-23 17:41 Fwd: Index on table when using DESC clause Yves Vindevogel <yves.vindevogel@implements.be>
2005-05-23 18:03 ` Re: Fwd: Index on table when using DESC clause Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-05-23 19:02 ` Re: Fwd: Index on table when using DESC clause Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-05-23 19:46 ` Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 6+ messages in thread
From: Oleg Bartunov @ 2005-05-23 19:46 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: Index on table when using DESC clause
2005-05-23 17:41 Fwd: Index on table when using DESC clause Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-05-23 20:27 ` Andrew Lazarus <andrew@pillette.com>
2005-05-23 21:18 ` Re: Fwd: Index on table when using DESC clause Yves Vindevogel <yves.vindevogel@implements.be>
1 sibling, 1 reply; 6+ messages in thread
From: Andrew Lazarus @ 2005-05-23 20:27 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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.
Attachments:
andrew.vcf (text/x-vcard, 296B) download
begin:vcard
fn:Andrew Lazarus
n:Lazarus;Andrew
org:Pillette Investment Management;Research and Development
adr;dom:;;3028 Fillmore;San Francisco;CA;94123
email;internet:andrew@pillette.com
title:Director
tel;work:800-366-0688
tel;fax:415-440-4093
url:http://www.pillette.com
version:2.1
end:vcard
* Re: Fwd: Index on table when using DESC clause
2005-05-23 17:41 Fwd: Index on table when using DESC clause Yves Vindevogel <yves.vindevogel@implements.be>
2005-05-23 20:27 ` Re: Fwd: Index on table when using DESC clause Andrew Lazarus <andrew@pillette.com>
@ 2005-05-23 21:18 ` Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 0 replies; 6+ messages in thread
From: Yves Vindevogel @ 2005-05-23 21:18 UTC (permalink / raw)
To: pgsql-performance; +Cc: Hendrik De Hertogh <hendrik.dehertogh@ecseed.com>
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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
end of thread, other threads:[~2005-05-23 21:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-23 17:41 Fwd: Index on table when using DESC clause Yves Vindevogel <yves.vindevogel@implements.be>
2005-05-23 18:03 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-05-23 19:02 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-05-23 19:46 ` Oleg Bartunov <oleg@sai.msu.su>
2005-05-23 20:27 ` Andrew Lazarus <andrew@pillette.com>
2005-05-23 21:18 ` Yves Vindevogel <yves.vindevogel@implements.be>
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