Re: formatting of SQL sent by PHP to postgres

On Thu, 30 Oct 2003, David Green wrote:

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

Postgresql's MVCC design makes it hard to use indexes for aggregate
functions. So, if you use something like max(id), postgresql literally
has to seq scan the table to find the max(id). MVCC allows postgresql to
handle massive parallel load. It causes some minor performance issues
like that that are hard to code around cleanly.

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

Sure, make sure your ODBC connector is set up to use cursors, so it
doesn't have to wait for the whole dataset to return before becoming
responsive.

Avoid lots of updates, i.e. don't issue a "update table set field=1" with
no where clause all the time.

Install the autovacuum daemon

Read the performance hints in both the performance tuning section of the
docs, and on varlena:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

And browse the performance mailing list, lots of good stuff in there.