Thread: functions, selects and the planner

functions, selects and the planner

From
"Matthew Dennis"
Date:
In PostgreSQL 8.3 lets say I have a table:

create table t(c1 int, c2 int, ts0 timestamp with time zone, ts1 timestamp with time zone, data varchar);

an index:

create index t_c1_c2_ts0_idx on t using btree(c1, c2, ts0 desc);

and a function:

create function f(_ts timestamp(0) with time zone, _c1 int, _c2 int) returns varchar as $$
declare
  _data varchar := null;
begin
  select into _data data from t where _ts >= ts0 and c1 = _c1 and c2 = _c2 and (ts1 is null or _ts < ts1);
  return _data;
end
$$ language plpgsql;

My question is when is the "select into _data" query planned/replanned?  I'm concerned that the query might only be planned once (e.g. the first time it's executed or when the function is first defined) and cached indefinitely.  The table t is initially empty, but grows at a fairly steady rate over time.  So if the table is essentially empty when the query is planned, a seqscan is certainly what the planner would do.  However, as time goes on and table t is filled with data and the stats change, will the query eventually be replanned?  If not, what are some suggested ways to handle this?

Re: functions, selects and the planner

From
Tom Lane
Date:
"Matthew Dennis" <mdennis@merfer.net> writes:
> My question is when is the "select into _data" query planned/replanned?  I'm
> concerned that the query might only be planned once (e.g. the first time
> it's executed or when the function is first defined) and cached
> indefinitely.  The table t is initially empty, but grows at a fairly steady
> rate over time.  So if the table is essentially empty when the query is
> planned, a seqscan is certainly what the planner would do.  However, as time
> goes on and table t is filled with data and the stats change, will the query
> eventually be replanned?

In 8.3, a replan will occur whenever VACUUM/ANALYZE update
pg_class.reltuples, so I think you don't need to worry --- at least not
unless you've disabled autovacuum and chosen a bad manual vacuuming
strategy.

            regards, tom lane