pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPREPARE and stuff
5+ messages / 4 participants
[nested] [flat]
* PREPARE and stuff
@ 2007-06-23 21:30 PFC <lists@peufeu.com>
0 siblings, 2 replies; 5+ messages in thread
From: PFC @ 2007-06-23 21:30 UTC (permalink / raw)
To: pgsql-performance
Suppose a web application with persistent database connections.
I have some queries which take longer to plan than to execute !
I with there was a way to issue a PREPARE (like "PERSISTENT PREPARE").
Now all Postgres connections would know that prepared statement foo( $1,
$2, $3 ) corresponds to some SQL query, but it wouldn't plan it yet. Just
like a SQL function.
When invoking EXECUTE foo( 1,2,3 ) on any given connection the statement
would get prepared and planned. Then on subsequent invocations I'd just
get the previously prepared plan.
Is this planned ?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PREPARE and stuff
@ 2007-06-23 21:51 Heikki Linnakangas <heikki@enterprisedb.com>
parent: PFC <lists@peufeu.com>
1 sibling, 0 replies; 5+ messages in thread
From: Heikki Linnakangas @ 2007-06-23 21:51 UTC (permalink / raw)
To: PFC <lists@peufeu.com>; +Cc: pgsql-performance
PFC wrote:
>
> Suppose a web application with persistent database connections.
> I have some queries which take longer to plan than to execute !
>
> I with there was a way to issue a PREPARE (like "PERSISTENT PREPARE").
> Now all Postgres connections would know that prepared statement foo(
> $1, $2, $3 ) corresponds to some SQL query, but it wouldn't plan it yet.
> Just like a SQL function.
> When invoking EXECUTE foo( 1,2,3 ) on any given connection the
> statement would get prepared and planned. Then on subsequent invocations
> I'd just get the previously prepared plan.
How would that be different from the current PREPARE/EXECUTE? Do you
mean you could PREPARE in one connection, and EXECUTE in another? If
you're using persistent connections, it wouldn't be any faster than
doing a PREPARE once in each connection.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PREPARE and stuff
@ 2007-06-23 21:55 Andreas Kostyrka <andreas@kostyrka.org>
0 siblings, 1 reply; 5+ messages in thread
From: Andreas Kostyrka @ 2007-06-23 21:55 UTC (permalink / raw)
To: PFC <lists@peufeu.com>; +Cc: pgsql-performance
Well, that's not completely trivial => the plan might depend upon the concrete value of $1,$2 and $3.
Andreas
-- Ursprüngl. Mitteil. --
Betreff: [PERFORM] PREPARE and stuff
Von: PFC <lists@peufeu.com>
Datum: 23.06.2007 21:31
Suppose a web application with persistent database connections.
I have some queries which take longer to plan than to execute !
I with there was a way to issue a PREPARE (like "PERSISTENT PREPARE").
Now all Postgres connections would know that prepared statement foo( $1,
$2, $3 ) corresponds to some SQL query, but it wouldn't plan it yet. Just
like a SQL function.
When invoking EXECUTE foo( 1,2,3 ) on any given connection the statement
would get prepared and planned. Then on subsequent invocations I'd just
get the previously prepared plan.
Is this planned ?
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PREPARE and stuff
@ 2007-06-23 22:02 Gregory Stark <stark@enterprisedb.com>
parent: PFC <lists@peufeu.com>
1 sibling, 0 replies; 5+ messages in thread
From: Gregory Stark @ 2007-06-23 22:02 UTC (permalink / raw)
To: PFC <lists@peufeu.com>; +Cc: pgsql-performance
"PFC" <lists@peufeu.com> writes:
> Suppose a web application with persistent database connections.
> I have some queries which take longer to plan than to execute !
There have periodically been discussions about a shared plan cache but
generally the feeling is that it would do more harm than good and there are no
plans to implement anything like that.
For a web application though you would expect to be executing the same queries
over and over again since you would be executing the same pages over and over
again. So just a regular prepared query ought to good for your needs.
You do not want to be reconnecting to the database for each page fetch.
Replanning queries is the least of the problems with that approach.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PREPARE and stuff
@ 2007-06-23 22:27 PFC <lists@peufeu.com>
parent: Andreas Kostyrka <andreas@kostyrka.org>
0 siblings, 0 replies; 5+ messages in thread
From: PFC @ 2007-06-23 22:27 UTC (permalink / raw)
To: Andreas Kostyrka <andreas@kostyrka.org>; +Cc: pgsql-performance
> Well, that's not completely trivial => the plan might depend upon the
> concrete value of $1,$2 and $3.
When you use PREPARE, it doesn't. I could live with that.
The purpose of this would be to have a library of "persistent prepared
statements" (just like lightweight functions) for your application, and
maximize the performance of persistent connections.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-06-23 22:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-23 21:30 PREPARE and stuff PFC <lists@peufeu.com>
2007-06-23 21:51 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-06-23 22:02 ` Gregory Stark <stark@enterprisedb.com>
2007-06-23 21:55 Re: PREPARE and stuff Andreas Kostyrka <andreas@kostyrka.org>
2007-06-23 22:27 ` PFC <lists@peufeu.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox