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
__________________________________________________ ______________________

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

re: formatting of SQL sent by PHP to postgres

Ken Guest <kguest@stockbyte.com> writes:[color=blue]
> 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?[/color]

Yes; no.
[color=blue]
> 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?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

re: formatting of SQL sent by PHP to postgres

On Thu, 30 Oct 2003, Ken Guest wrote:
[color=blue]
> 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?[/color]

Yes, you can get rid of it. No, it won't make any real difference.
[color=blue]
> 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?[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

re: formatting of SQL sent by PHP to postgres

On Thu, 30 Oct 2003, Scott Marlowe wrote:
[color=blue]
> "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..."[/color]


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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

re: formatting of SQL sent by PHP to postgres

On Thu, 30 Oct 2003, David Green wrote:
[color=blue]
>
> On Thu, 30 Oct 2003, Scott Marlowe wrote:
>[color=green]
> > "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..."[/color]
>
>
> 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.[/color]

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.
[color=blue]
> 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...[/color]

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/Gener...bits/perf.html

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

re: formatting of SQL sent by PHP to postgres

On Thu, 2003-10-30 at 15:57, scott.marlowe wrote:[color=blue]
> On Thu, 30 Oct 2003, David Green wrote:
>[color=green]
> >
> > On Thu, 30 Oct 2003, Scott Marlowe wrote:
> >[color=darkred]
> > > "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..."[/color]
> >
> >
> > 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.[/color]
>
> 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.
>[color=green]
> > 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...[/color]
>
> 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/Gener...bits/perf.html
>
> And browse the performance mailing list, lots of good stuff in there.
>[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

re: formatting of SQL sent by PHP to postgres

"scott.marlowe" <scott.marlowe@ihs.com> writes:
[color=blue]
> 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.[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

re: formatting of SQL sent by PHP to postgres

Le Jeudi 30 Octobre 2003 21:19, David Green a écrit :[color=blue]
> On Thu, 30 Oct 2003, Scott Marlowe wrote:[color=green]
> > "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..."[/color]
>
> 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..[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend