From: PFC <lists@peufeu.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Cached Query Plans
Date: Sat, 12 Apr 2008 11:13:46 +0200
Message-ID: <op.t9hqk8z4cigqcu@apollo13.peufeu.com> (raw)
> Well if you're caching per-connection then it doesn't really matter
> whether
> you do it on the client side or the server side, it's pretty much
> exactly the
> same problem.
Actually I thought about doing it on the server since it would then also
work with connection pooling.
Doing it on the client means the client has to maintain state, which is
not possible in a pool...
> Unsurprisingly most drivers do precisely what you're describing. In Perl
> DBI
> for example you just change $dbh->prepare("") into
> $dbh->prepare_cached("")
> and it does exactly what you want. I would expect the PHP drivers to have
> something equivalent.
Well, PHP doesn't.
Perhaps I should patch PHP instead...
Or perhaps this feature should be implemented in pgpool or pgbouncer.
>> But, using prepared statements with persistent connections is messy,
>> because you never know if the connection is new or not,
> If you were to fix *that* then both this problem and others (such as
> setting up desired SET-parameter values) would go away.
True. Languages that keep a long-running context (like application
servers etc) can do this easily.
Although in the newer versions of PHP, it's not so bad, pconnect seems to
work (ie. it will issue ROLLBACKs when the script dies, reset session
variables like enable_indexscan, etc), so the only remaining problem seems
to be prepared statements.
And again, adding a method for the application to know if the persistent
connection is new or not, will not work in a connection pool...
Perhaps a GUC flag saying EXECUTE should raise an error but not kill the
current transaction if the requested prepared statement does not exist ?
Then the application would issue a PREPARE. It could also raise a
non-fatal error when the tables have changed (column added, for instance)
so the application can re-issue a PREPARE.
But I still think it would be cleaner to do it in the server.
Also, I rethought about what Gregory Stark said :
> The contention on the shared cache is likely to negate much of the
> planning
> savings but I think it would still be a win.
If a shared plan cache is implemented, it will mostly be read-only, ie.
when the application is started, new queries will come, so the plans will
have to be written to the cache, but then once the cache contains
everything it needs, it will not be modified that often, so I wouldn't
think contention would be such a problem...
> It's not so easy as all that. Consider search_path. Consider temp
> tables.
Temp tables : I thought plan revalidation took care of this ?
(After testing, it does work, if a temp table is dropped and recreated,
PG finds it, although of course if a table is altered by adding a column
for instance, it logically fails).
search_path: I suggested to either put the search_path in the cache key
along with the SQL string, or force queries to specify schema.table for
all tables.
It is also possible to shoot one's foot with the current PREPARE (ie.
search_path is used to PREPARE but of course not for EXECUTE), and also
with plpgsql functions (ie. the search path used to compile the function
is the one that is active when it is compiled, ie at its first call in the
current connection, and not the search path that was active when the
function was defined)...
SET search_path TO DEFAULT;
CREATE SCHEMA a;
CREATE SCHEMA b;
CREATE TABLE a.test( v TEXT );
CREATE TABLE b.test( v TEXT );
INSERT INTO a.test VALUES ('This is schema a');
INSERT INTO b.test VALUES ('This is schema b');
CREATE OR REPLACE FUNCTION test_search_path()
RETURNS SETOF TEXT
LANGUAGE plpgsql
AS
$$
DECLARE
x TEXT;
BEGIN
FOR x IN SELECT v FROM test LOOP
RETURN NEXT x;
END LOOP;
END;
$$;
test=> SET search_path TO a,public;
test=> SELECT * FROM test_search_path();
test_search_path
------------------
This is schema a
test=> \q
$ psql test
test=> SET search_path TO b,public;
test=> SELECT * FROM test_search_path();
test_search_path
------------------
This is schema b
test=> SET search_path TO a,public;
test=> SELECT * FROM test_search_path();
test_search_path
------------------
This is schema b
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com
Subject: Re: Cached Query Plans
In-Reply-To: <op.t9hqk8z4cigqcu@apollo13.peufeu.com>
References: <op.t9hqk8z4cigqcu@apollo13.peufeu.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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