Skip site navigation (1) Skip section navigation (2)

Re: Cached Query Plans

From: PFC <lists(at)peufeu(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Cached Query Plans
Date: 2008-04-12 09:13:46
Message-ID: op.t9hqk8z4cigqcu@apollo13.peufeu.com (view raw or flat)
Thread:
Lists: pgsql-performance
> 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






pgsql-performance by date

Next:From: Adrian MoiseyDate: 2008-04-14 06:29:56
Subject: db size
Previous:From: Greg SmithDate: 2008-04-11 20:26:23
Subject: Re: Performance is low Postgres+Solaris

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group