From: Andres Freund <andres@anarazel.de>
To: Doug Doole <ddoole@salesforce.com>
Cc: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Cc: Serge Rielau <serge@rielau.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Cached plans and statement generalization
Date: Tue, 25 Apr 2017 15:34:48 -0700
Message-ID: <20170425223448.amnhdludwondarph@alap3.anarazel.de> (raw)
In-Reply-To: <CAP6UvaNvFYprWdghZ4=MFUFLczzj1br9CX+vAQFz8wAGd14yug@mail.gmail.com>
References: <f0bfcdd6-dba3-e9a8-2108-146e1f880839@postgrespro.ru>
<CBF37923-CDA5-48EF-9E9E-4A338D7988ED@rielau.com>
<1f4c1a22-ecbe-811d-4695-ee4701b8f6c7@postgrespro.ru>
<e85343e6-5ab5-447c-9995-5cce992fedf3@rielau.com>
<58FFB391.5080101@postgrespro.ru>
<C0064A85-7CE9-44E6-B14F-98E541B328FD@rielau.com>
<58FFB5F0.2020900@postgrespro.ru>
<CAP6UvaMjiumn5-Lre_w11j=ZAOB9auO1x=-meTKsWRfcsQ7Pcw@mail.gmail.com>
<20170425214746.3mngxgbo7yndfgax@alap3.anarazel.de>
<CAP6UvaNvFYprWdghZ4=MFUFLczzj1br9CX+vAQFz8wAGd14yug@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Hi,
(FWIW, on this list we don't do top-quotes)
On 2017-04-25 22:21:22 +0000, Doug Doole wrote:
> Plan invalidation was no different than for any SQL statement. DB2 keeps a
> list of the objects the statement depends on. If any of the objects changes
> in an incompatible way the plan is invalidated and kicked out of the cache.
>
> I suspect what is more interesting is plan lookup. DB2 has something called
> the "compilation environment". This is a collection of everything that
> impacts how a statement is compiled (SQL path, optimization level, etc.).
> Plan lookup is done using both the statement text and the compilation
> environment. So, for example, if my path is DOUG, MYTEAM, SYSIBM and your
> path is ANDRES, MYTEAM, SYSIBM we will have different compilation
> environments. If we both issue "SELECT * FROM T" we'll end up with
> different cache entries even if T in both of our statements resolves to
> MYTEAM.T. If I execute "SELECT * FROM T", change my SQL path and then
> execute "SELECT * FROM T" again, I have a new compilation environment so
> the second invocation of the statement will create a new entry in the
> cache. The first entry is not kicked out - it will still be there for
> re-use if I change my SQL path back to my original value (modulo LRU for
> cache memory management of course).
It's not always that simple, at least in postgres, unless you disregard
search_path. Consider e.g. cases like
CREATE SCHEMA a;
CREATE SCHEMA b;
CREATE TABLE a.foobar(somecol int);
SET search_patch = 'b,a';
SELECT * FROM foobar;
CREATE TABLE b.foobar(anothercol int);
SELECT * FROM foobar; -- may not be cached plan from before!
it sounds - my memory of DB2 is very faint, and I never used it much -
like similar issues could arise in DB2 too?
Greetings,
Andres Freund
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andres@anarazel.de, ddoole@salesforce.com, k.knizhnik@postgrespro.ru, serge@rielau.com
Subject: Re: Cached plans and statement generalization
In-Reply-To: <20170425223448.amnhdludwondarph@alap3.anarazel.de>
* 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