Thread: formatting of SQL sent by PHP to postgres
Folks, I have a question or two regarding PHP and Postgres on the issue of speed: 1. Is the semicolon at the end of SQL superflous when sent to Postgres? Should it make much of a difference if I removed it? 2. A lot of SQL issued have white space characters (newlines, tabs and spaces) present - does this have any [major] impact on how quick postgres gets to execute the SQL and return results? k. -- Ken Guest Senior Developer Stockbyte Royalty Free Photos http://www.stockbyte.com ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com ________________________________________________________________________
Ken Guest <kguest@stockbyte.com> writes:
> 1. Is the semicolon at the end of SQL superflous when sent to Postgres?
> Should it make much of a difference if I removed it?
Yes; no.
> 2. A lot of SQL issued have white space characters (newlines, tabs and
> spaces) present - does this have any [major] impact on how quick
> postgres gets to execute the SQL and return results?
No. I doubt you could measure the impact at all ... unless you are
talking many kilobytes of whitespace, in which case the data
transmission overhead might be noticeable.
regards, tom lane
On Thu, 2003-10-30 at 15:34, 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... that's a neat trick Scott, Thanks for that and I appreciate your reply :) k. -- Ken Guest Senior Developer Stockbyte Royalty Free Photos http://www.stockbyte.com ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com ________________________________________________________________________
On Thu, 30 Oct 2003, Ken Guest wrote: > Folks, > I have a question or two regarding PHP and Postgres on the issue of > speed: > 1. Is the semicolon at the end of SQL superflous when sent to Postgres? > Should it make much of a difference if I removed it? Yes, you can get rid of it. No, it won't make any real difference. > 2. A lot of SQL issued have white space characters (newlines, tabs and > spaces) present - does this have any [major] impact on how quick > postgres gets to execute the SQL and return results? Other than the very tiny increase in time needed to send the extra blank spaces across the wire, no, it won't have any real effect on the performance of the database. 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...
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.
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.
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
"scott.marlowe" <scott.marlowe@ihs.com> writes: > 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. That postgres doesn't use indexes for max/min is really not related to MVCC. It would be a lot of work to do so and involve changes to lots of places and it just hasn't been done yet. But it can be done and would be just as hard without MVCC. You're thinking of caching aggregate results for things like count(). That's where supporting transactions makes it hard. But that's really unrelated to min/max and indexes. -- greg
Le Jeudi 30 Octobre 2003 21:19, David Green a écrit : > 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.. This is a good thing but remember that will run only if you have an index on the "Num" Column ... and if you have not null value in the field ! Otherwise you will get a better result with MAX function. regards, -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902