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
