Thread: Clarification on PL/pgSQL query plan caching
We were experiencing insert slowdowns at the beginning of the day when we add new tables. As part of our data insert process, we have a read function and we decided to modify it to use EXECUTE to avoid plan caching. Our assumption was was the adding the table would invalidate the plan for the current running connection.
This fixed our issue, but the surprising side affect is that it took 100 sec off of our runtime at the other parts of the day.
I have added the before and after examples, I am wondering on the *why* writing it in the before example is bad? Function only has one plan?
BEFORE:
IF ptype = 'data' THEN
SELECT lasttime, lastval INTO mcurr FROM d_current WHERE lasttime > pdate - '1 hour'::interval AND id = pid ORDER BY lasttime DESC LIMIT 1;
ELSIF ptype = 'idata' THEN
SELECT lasttime, lastval INTO mcurr FROM c_current WHERE lasttime > pdate - '1 hour'::interval AND id = pid ORDER BY lasttime DESC LIMIT 1;
ELSIF ptype = 'ddata' THEN
SELECT lasttime, lastval INTO mcurr FROM c_current WHERE lasttime > pdate - '1 hour'::interval AND id = pid ORDER BY lasttime DESC LIMIT 1;
ELSIF ptype = 'ldata' THEN
SELECT lasttime, lastvall INTO mcurr FROM dl_current WHERE lasttime > pdate - '1 hour'::interval AND id = pid ORDER BY lasttime DESC LIMIT 1;
ELSE
SELECT lasttime, lastval INTO mcurr FROM current WHERE lasttime > pdate - '1 hour'::interval AND id = pid ORDER BY lasttime DESC LIMIT 1;
END IF;
IF NOT FOUND THEN
-- RAISE DEBUG 'No previous data found for pgid=%', pgid;
return NULL;
END IF;
AFTER:
limiter timestamptz := pdate - '1 hour'::interval;
IF ptype = 'data' THEN
table_name := 'd_current';
ELSIF ptype = 'ldata' THEN
table_name := 'dl_current';
ELSIF ptype = 'idata' THEN
table_name := 'c_current';
ELSIF ptype = 'ddata' THEN
table_name := 'c_current';
END IF;
EXECUTE 'SELECT lasttime, lastval FROM ' || table_name || ' WHERE lasttime > $1 AND id = $2 ORDER BY lasttime DESC LIMIT 1' INTO mcurr USING limiter, pid;
IF mcurr IS NULL THEN
-- RAISE DEBUG 'No previous data found for pgid=%', pgid;
return NULL;
END IF;
Thanks,
George Woodring
iGLASS Networks
www.iglass.net
www.iglass.net