Thread: Cache plan invalidation
The current TODO list has: Dependency Checking===================* Flush cached query plans when the dependent objects change, when the cardinalityof parameters changes dramatically, or when new ANALYZE statistics are available A more complex solution wouldbe to save multiple plans for different cardinality and use the appropriate plan based on the EXECUTE values.* Trackdependencies in function bodies and recompile/invalidate This is particularly important for references to temporarytables in PL/PgSQL because PL/PgSQL caches query plans. The only workaround in PL/PgSQL is to use EXECUTE. Onecomplexity is that a function might itself drop and recreate dependent tables, causing it to invalidate its own queryplan. Which of these are done or not done? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > The current TODO list has: > > Dependency Checking > =================== > > * Flush cached query plans when the dependent objects change, > when the cardinality of parameters changes dramatically, or > when new ANALYZE statistics are available > > A more complex solution would be to save multiple plans for different > cardinality and use the appropriate plan based on the EXECUTE values. > > * Track dependencies in function bodies and recompile/invalidate > > This is particularly important for references to temporary tables > in PL/PgSQL because PL/PgSQL caches query plans. The only workaround > in PL/PgSQL is to use EXECUTE. One complexity is that a function > might itself drop and recreate dependent tables, causing it to > invalidate its own query plan. > > Which of these are done or not done? Also, is this done: * Invalidate prepared queries, like INSERT, when the table definition is altered -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Bruce Momjian wrote:
>> The current TODO list has:
>>
>> Dependency Checking
>> ===================
>>
>> * Flush cached query plans when the dependent objects change,
>> when the cardinality of parameters changes dramatically, or
>> when new ANALYZE statistics are available
>>
>> A more complex solution would be to save multiple plans for different
>> cardinality and use the appropriate plan based on the EXECUTE values.
This is partially done --- you'll have to split it into multiple items
if you want to preserve the bit about keeping different plans for
different parameter values. Note that in the current code, any VACUUM
or ANALYZE on a table will force relcache inval and hence replan; see
vac_update_relstats. So the only case not covered as far as
non-parameterized queries go is large growth of a table without any
vacuuming or analyzing ... and you're going to have problems anyway
if you don't analyze after loading a table. We may in fact find that
our problem is now too many replans rather than too few.
>> * Track dependencies in function bodies and recompile/invalidate
>>
>> This is particularly important for references to temporary tables
>> in PL/PgSQL because PL/PgSQL caches query plans.
This is done.
> Also, is this done:
> * Invalidate prepared queries, like INSERT, when the table definition
> is altered
This too.
regards, tom lane
I removed the cardinality item and marked the others as done: * -Flush cached query plans when the dependent objects change or when new ANALYZE statistics are available* -Track dependenciesin function bodies and recompile/invalidate* -Invalidate prepared queries, like INSERT, when the table definition is altered Let's see if the cardinality issue is still needed after this release. --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Bruce Momjian wrote: > >> The current TODO list has: > >> > >> Dependency Checking > >> =================== > >> > >> * Flush cached query plans when the dependent objects change, > >> when the cardinality of parameters changes dramatically, or > >> when new ANALYZE statistics are available > >> > >> A more complex solution would be to save multiple plans for different > >> cardinality and use the appropriate plan based on the EXECUTE values. > > This is partially done --- you'll have to split it into multiple items > if you want to preserve the bit about keeping different plans for > different parameter values. Note that in the current code, any VACUUM > or ANALYZE on a table will force relcache inval and hence replan; see > vac_update_relstats. So the only case not covered as far as > non-parameterized queries go is large growth of a table without any > vacuuming or analyzing ... and you're going to have problems anyway > if you don't analyze after loading a table. We may in fact find that > our problem is now too many replans rather than too few. > > >> * Track dependencies in function bodies and recompile/invalidate > >> > >> This is particularly important for references to temporary tables > >> in PL/PgSQL because PL/PgSQL caches query plans. > > This is done. > > > Also, is this done: > > > * Invalidate prepared queries, like INSERT, when the table definition > > is altered > > This too. > > regards, tom lane -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +