Re: formatting of SQL sent by PHP to postgres

On Thu, 2003-10-30 at 15:57, scott.marlowe wrote:
> 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.
>

Isn't sql server one of the databases that does rewriteing of (what we
consider) explicit join plans? If so your "style" of sql queries may be
slower in postgresql, but theres a fix for this in postgresql 7.4 (which
should be released soon, so probably worth testing on)

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL