Autore-plan cached queries when table definitions change or stats updated

digoal

最近从一位朋友那里得知在PG中使用绑定变量查询非常慢，不使用绑定变量的查询正常。
  在GreenPlum中:当WHERE条件中使用变量时间如now()时和静态时间如'2010-12-24'时执行计划不一致的情况(特指时间分区的分区表)，不过这个已经在GreenPlum的release中有说明。
  PostgreSQL中到目前为止没有类似案例，因此怀疑是执行计划的问题,(postgresql 8.4以后可以跟踪到SQL的执行计划信息,需修改参数)

Automatically re-plan cached queries when table definitions change or statistics are updated
  这句话来自PostgreSQL 8.3 Release Notes
当表的定义或统计信息改变时，cached queries将重新生成执行计划。
届时影响绑定变量的SQL。此时可能改变执行计划，出现和以前执行计划不一致的情况，严重的影响数据库性能。
如果绑定变量的SQL出现执行计划不正常，建议重新生成统计信息。

另外可以通过discard命令释放CACHE资源(仅仅针对当前SESSION)

DISCARD { ALL | PLANS | TEMPORARY | TEMP }

Description

DISCARD releases internal resources associated with a database session. These resources are normally released at the end of the session.

DISCARD TEMP drops all temporary tables created in the current session. DISCARD PLANS releases all internally cached query plans. DISCARD ALL resets a session to its original state, discarding temporary resources and resetting session-local configuration changes.

例如

Releases all temporary resources associated with the current session and resets the session to its initial state. Currently, this has the same effect as executing the following sequence of statements:

SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;