Hi Marc,

M. Bastin wrote:
Thanks for the explanation. I've only been using Postgres
via the psql monitor and client applications (most of them
written in Python, some in Perl), so I'm not familiar with
the underlying client-server protocol.

You are right that the EXECUTE statement still has to be
parsed. However, I think the pasrsing overhead is small,
because the EXECUTE command has a very simple structure
("EXECUTE <plan> <arguments>"), as opposed to, say, the
very complicated synopsis of a SELECT command. Apart
from that, the overhead of the query planner is probably
much bigger, so using PREPARE + EXECUTE is probably still
a great win, I think.

I just wonder ... I'm currently writing a client app (in
Python) which has to insert quite a lot of things. This
is for a network traffic accounting system. In particular,
the program reads accumulated accounting data from a file,
pre-processes it and creates appropriate INSERT statements
(up to several hundreds or even thousands per session).
I wonder if it will be worth to PREPARE those inserts and
the EXECUTE them. But I guess it won't make much of a
difference, because the INSERT statements are very simple
anyway.

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

With Perl you can manipulate text, interact with programs, talk over
networks, drive Web pages, perform arbitrary precision arithmetic,
and write programs that look like Snoopy swearing.

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

At 10:45 PM +0200 7/15/04, Oliver Fromme wrote:
It's 16,000 vs 1,500 microseconds on my system, plus the time for the
client to receive and parse the data which is the same in both cases.

Certainly for complex queries, but not for simple ones like "SELECT *
FROM mytable WHERE numcolumn > $1;"
Parsing a simple SELECT like this seems to take about the same time
as parsing an EXECUTE query.

I don't think you'd win anything by replacing INSERT with EXECUTE either.

I'm not familiar with Python but if it allows you to do a "COPY
mytable FROM STDIN;" then I would strongly recommend you'd use that
instead of INSERT. You build your data in a file and then you COPY
that file into your table in one step. You can import millions of
records in a couple of minutes like that. (You need to drop your
indexes on that table first and recreate them afterwards.)

Best,

Marc

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

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