Re: formatting of SQL sent by PHP to postgres

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..

I'm running pgsql v7.3.2 on redhat 9.

Also, are there any other "tricks" for optimizing this way? I have a vb app
I'm porting to PostgreSQL from SQL Server and it seems a lot of the queries,
etc take a lot longer... I'm starting to think it may be ODBC or something
slowing me up but that I can ask about on the other mailing list...


David Green
Sage Automation, Inc.