Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 236752E003D for ; Sat, 12 Apr 2008 06:09:33 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 17889-08 for ; Sat, 12 Apr 2008 06:09:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from h07.llord.com (unknown [195.140.142.194]) by developer.postgresql.org (Postfix) with ESMTP id 25C692E0032 for ; Sat, 12 Apr 2008 06:09:30 -0300 (ADT) Received: from par69-8-88-161-102-87.fbx.proxad.net ([88.161.102.87] helo=apollo13.peufeu.com) by h07.llord.com with esmtpa (Exim 4.68) (envelope-from ) id 1Jkbjj-0002dF-Ub for pgsql-performance@postgresql.org; Sat, 12 Apr 2008 11:09:28 +0200 Date: Sat, 12 Apr 2008 11:13:46 +0200 To: "pgsql-performance@postgresql.org" Subject: Re: Cached Query Plans From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 Content-Transfer-Encoding: Quoted-Printable Message-ID: User-Agent: Opera Mail/9.24 (Linux) X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - h07.llord.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - peufeu.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.903 tagged_above=0 required=5 tests=AWL=0.803, RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200804/162 X-Sequence-Number: 29655 > 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 als= o = 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 Pe= rl = > 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 h= ave > 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 see= ms = to be prepared statements. And again, adding a method for the application to know if the persisten= t = 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 th= e = 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 wil= l = 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 t= he = 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=3D> SET search_path TO a,public; test=3D> SELECT * FROM test_search_path(); test_search_path ------------------ This is schema a test=3D> \q $ psql test test=3D> SET search_path TO b,public; test=3D> SELECT * FROM test_search_path(); test_search_path ------------------ This is schema b test=3D> SET search_path TO a,public; test=3D> SELECT * FROM test_search_path(); test_search_path ------------------ This is schema b