Skip site navigation (1) Skip section navigation (2)

Re: formatting of SQL sent by PHP to postgres

From: Hervé Piedvache <herve(at)elma(dot)fr>
To: "David Green" <david(at)sagerobot(dot)com>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: formatting of SQL sent by PHP to postgres
Date: 2003-10-31 08:55:30
Message-ID: 200310310955.30479.herve@elma.fr (view raw or flat)
Thread:
Lists: pgsql-general
Le Jeudi 30 Octobre 2003 21:19, David Green a écrit :
> On Thu, 30 Oct 2003, Scott Marlowe wrote:
> > "It's far more likely that optimizing your SQL queries will yield the
> > greatest increase in performance.  Things like replacing "select max(id)
> > from table" with "select id from table order by id desc limit 1" etc..."
>
> When I first read this I was surprised that this kind of change could even
> make
> a difference. I tested it and it makes a lot of difference.
>
> Ex.
> On a table with 21,000 records I ran 2 queries. One using "Max(Num)" and
> one using the "order by num desc limit 1". The "Max(Num)" query took 51
> msec and the other took 0.09 msec. I tried the same thing on SQL Server and
> the 2 queries run in exactly the same amount of time. Why does it make so
> much of a difference in PostgreSQL? I did notice in the query plan, the 
> second query  was able to use the index on the Num field - this may be the
> speed difference..

This is a good thing but remember that will run only if you have an index on 
the "Num" Column ... and if you have not null value in the field !
Otherwise you will get a better result with MAX function.

regards,
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902


In response to

pgsql-general by date

Next:From: Shridhar DaithankarDate: 2003-10-31 08:56:43
Subject: Re: slow query performance
Previous:From: Dave WeaverDate: 2003-10-31 08:33:18
Subject: Re: slow query performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group