Re: formatting of SQL sent by PHP to postgres
От | Robert Treat |
---|---|
Тема | Re: formatting of SQL sent by PHP to postgres |
Дата | |
Msg-id | 1067551233.2069.32854.camel@camel обсуждение исходный текст |
Ответ на | Re: formatting of SQL sent by PHP to postgres ("scott.marlowe" <scott.marlowe@ihs.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: