functions, selects and the planner
От | Matthew Dennis |
---|---|
Тема | functions, selects and the planner |
Дата | |
Msg-id | e94d85500806142039p74995552p9ee0ec128161b27b@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: functions, selects and the planner
|
Список | pgsql-general |
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?
В списке pgsql-general по дате отправления: