Horton

Thread

  1. Slow performance on MAX(primary_key)

    Keith Gray <keith@heart.com.au> — 2002-10-15T05:01:58Z

    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

  2. Re: Slow performance on MAX(primary_key)

    Ludwig Lim <lud_nowhere_man@yahoo.com> — 2002-10-15T07:16:28Z

    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

  3. Re: Slow performance on MAX(primary_key)

    Keith Gray <keith@heart.com.au> — 2002-10-15T07:47:13Z

    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

  4. Re: Slow performance on MAX(primary_key)

    Richard Huxton <dev@archonet.com> — 2002-10-15T08:23:15Z

    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

  5. Re: Slow performance on MAX(primary_key)

    Keith Gray <keith@heart.com.au> — 2002-10-17T00:46:59Z

    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

  6. Re: Slow performance on MAX(primary_key)

    Charles H. Woloszynski <chw@clearmetrix.com> — 2002-10-17T01:09:11Z

    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

  7. Re: Slow performance on MAX(primary_key)

    Richard Huxton <dev@archonet.com> — 2002-10-17T09:26:52Z

    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