From: | "Matthew Dennis" <mdennis(at)merfer(dot)net> |
---|---|
To: | PGSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | functions, selects and the planner |
Date: | 2008-06-15 03:39:17 |
Message-ID: | e94d85500806142039p74995552p9ee0ec128161b27b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-06-15 03:58:05 | Re: functions, selects and the planner |
Previous Message | Tom Lane | 2008-06-15 00:41:26 | Re: dblink() cursor error/issue (TopMemoryContext) |