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

Re: formatting of SQL sent by PHP to postgres

From: "David Green" <david(at)sagerobot(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Re: formatting of SQL sent by PHP to postgres
Date: 2003-10-30 20:19:31
Message-ID: PJEHLPJNJNGKEKGJMAGDKEMACNAA.david@sagerobot.com (view raw or flat)
Thread:
Lists: pgsql-general
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.


In response to

Responses

pgsql-general by date

Next:From: scott.marloweDate: 2003-10-30 20:57:16
Subject: Re: formatting of SQL sent by PHP to postgres
Previous:From: Josh BerkusDate: 2003-10-30 18:26:05
Subject: Need Italian Help!

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