Thread: functions, selects and the planner
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?
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?
"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