pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow performance on MAX(primary_key)
7+ messages / 4 participants
[nested] [flat]
* Slow performance on MAX(primary_key)
@ 2002-10-15 05:01 Keith Gray <keith@heart.com.au>
0 siblings, 1 reply; 7+ messages in thread
From: Keith Gray @ 2002-10-15 05:01 UTC (permalink / raw)
To: pgsql-sql
Help,
I have just been comparing some large table performance
under 7.1 using the
select max(primary key)from table;
We are using this for various functions including sequence.
It is taking 9 seconds to return this from around 1 million records.
Shouldn't this be an instantaneous lookup?
--
Keith Gray
Technical Services Manager
Heart Consulting Services
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance on MAX(primary_key)
@ 2002-10-15 07:16 Ludwig Lim <lud_nowhere_man@yahoo.com>
parent: Keith Gray <keith@heart.com.au>
0 siblings, 1 reply; 7+ messages in thread
From: Ludwig Lim @ 2002-10-15 07:16 UTC (permalink / raw)
To: Keith Gray <keith@heart.com.au>; +Cc: pgsql-sql
Hi Keith:
--- Keith Gray <keith@heart.com.au> wrote:
> Help,
>
> I have just been comparing some large table
> performance
> under 7.1 using the
>
> select max(primary key)from table;
>
> We are using this for various functions including
> sequence.
>
Try using the following as alternative :
SELECT primary_key
FROM table
ORDER BY primary_key desc
LIMIT 1;
This should work if primary_key is indexes.
As of now, Max() doesn't utilizes the indices hence
it always do a sequential scan.
Hope that helps,
regards,
ludwig.
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance on MAX(primary_key)
@ 2002-10-15 07:47 Keith Gray <keith@heart.com.au>
parent: Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Keith Gray @ 2002-10-15 07:47 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: pgsql-sql
Ludwig Lim wrote:
>>I have just been comparing some large table
>>performance under 7.1 using the
>>
>> select max(primary key)from table;
>>
>
> Try using the following as alternative :
>
> SELECT primary_key
> FROM table
> ORDER BY primary_key desc
> LIMIT 1;
>
> This should work if primary_key is indexes.
>
> As of now, Max() doesn't utilizes the indices hence
> it always do a sequential scan.
Thanks Ludwig,
That does help performance, but I was using a "standard"
SQL command wrapped in a VB6 ADO ODBC program.
Is this likely to be sorted in 7.2 ?
Is anyone looking at this?
--
Keith Gray
Technical Services Manager
Heart Consulting Services
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance on MAX(primary_key)
@ 2002-10-15 08:23 Richard Huxton <dev@archonet.com>
parent: Keith Gray <keith@heart.com.au>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2002-10-15 08:23 UTC (permalink / raw)
To: Keith Gray <keith@heart.com.au>; +Cc: pgsql-sql
On Tuesday 15 Oct 2002 8:47 am, Keith Gray wrote:
> Ludwig Lim wrote:
> > As of now, Max() doesn't utilizes the indices hence
> > it always do a sequential scan.
>
> Thanks Ludwig,
>
> That does help performance, but I was using a "standard"
> SQL command wrapped in a VB6 ADO ODBC program.
>
> Is this likely to be sorted in 7.2 ?
> Is anyone looking at this?
As I understand, the problem is that the optimisation only applies for simple
cases, and for certain aggregate functions (e.g. not sum()). This means that
the parser would need special-case code to spot these cases, along with tags
for those functions that can be optimised. Thinking further, it might also
vary from type to type. Given that there is a simple workaround and the need
for the optimisation to be added cleanly to the code I believe this has a
fairly low priority.
There is a todo list on the developers' side of the website which has a list
of changes in upcoming releases, you could check there for details.
--
Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance on MAX(primary_key)
@ 2002-10-17 00:46 Keith Gray <keith@heart.com.au>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 7+ messages in thread
From: Keith Gray @ 2002-10-17 00:46 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Richard Huxton wrote:
>>> As of now, Max() doesn't utilizes the indices hence
>>>it always do a sequential scan.
>>Is this likely to be sorted in 7.2 ?
>>Is anyone looking at this?
> As I understand, the problem is that the optimisation only applies for simple
> cases...
Getting MIN() adn MAX() seems fairly trivial to me.
When is on an index or more importantly Primary
Key it must be a common SQL.
Would it be possible in the code to look at
the field in MIN() or MAX() and if it is
indexed use a similar method to the suggested
SQL work around?
Can I help this to happen?
--
Keith Gray
Technical Services Manager
Heart Consulting Services
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance on MAX(primary_key)
@ 2002-10-17 01:09 Charles H. Woloszynski <chw@clearmetrix.com>
parent: Keith Gray <keith@heart.com.au>
1 sibling, 0 replies; 7+ messages in thread
From: Charles H. Woloszynski @ 2002-10-17 01:09 UTC (permalink / raw)
To: Keith Gray <keith@heart.com.au>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
Keith:
I think it would be great to get the optimizer to do something smart on
such a simple (and common) query. I am porting an app to Postgresql and
I am not looking forward to having to fix all the postgres-ism that
seem trivial like this. Postgres gets a bad rap for this kinda simple
qweak that makes out of the box queries perform slowly and I'd like to
help improve the image.
Let me know if you need another tester for the fixes. Currently on 7.2.
here (7.2.3. I memory serves correct).
Charlie
Keith Gray wrote:
> Richard Huxton wrote:
>
>>>> As of now, Max() doesn't utilizes the indices hence
>>>> it always do a sequential scan.
>>>
>
>
>>> Is this likely to be sorted in 7.2 ?
>>> Is anyone looking at this?
>>
>
>
>> As I understand, the problem is that the optimisation only applies
>> for simple cases...
>
>
>
> Getting MIN() adn MAX() seems fairly trivial to me.
>
> When is on an index or more importantly Primary
> Key it must be a common SQL.
>
> Would it be possible in the code to look at
> the field in MIN() or MAX() and if it is
> indexed use a similar method to the suggested
> SQL work around?
>
> Can I help this to happen?
>
>
>
--
Charles H. Woloszynski
ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015
tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow performance on MAX(primary_key)
@ 2002-10-17 09:26 Richard Huxton <dev@archonet.com>
parent: Keith Gray <keith@heart.com.au>
1 sibling, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2002-10-17 09:26 UTC (permalink / raw)
To: Keith Gray <keith@heart.com.au>; +Cc: pgsql-sql
On Thursday 17 Oct 2002 12:46 am, Keith Gray wrote:
> Getting MIN() adn MAX() seems fairly trivial to me.
>
> When is on an index or more importantly Primary
> Key it must be a common SQL.
>
> Would it be possible in the code to look at
> the field in MIN() or MAX() and if it is
> indexed use a similar method to the suggested
> SQL work around?
>
> Can I help this to happen?
Subscribe to pgsql-hackers and talk through a proposed hack there. The
developers always seem happy to receive contributions (you've just got to
look at the contrib folder for evidence).
--
Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-10-17 09:26 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-15 05:01 Slow performance on MAX(primary_key) Keith Gray <keith@heart.com.au>
2002-10-15 07:16 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-10-15 07:47 ` Keith Gray <keith@heart.com.au>
2002-10-15 08:23 ` Richard Huxton <dev@archonet.com>
2002-10-17 00:46 ` Keith Gray <keith@heart.com.au>
2002-10-17 01:09 ` Charles H. Woloszynski <chw@clearmetrix.com>
2002-10-17 09:26 ` Richard Huxton <dev@archonet.com>
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