Thread: plpgsql functions and the planner
Do SQL statements inside of plpgsql functions get planned upon every execution, only when the function is first executed/defined, or something else entirely?
For example, suppose I have a table foo and a function bar. Function bar executes some SQL statements (select/insert/update) against table foo using various indexed columns. When the function is created and first executed, table foo is near empty so the statements in function bar use seqscans. Then table foo is filled with many rows. Now, when bar is executed again, will PG (8.3.1) know that a seqscan is no longer reasonable?
For example, suppose I have a table foo and a function bar. Function bar executes some SQL statements (select/insert/update) against table foo using various indexed columns. When the function is created and first executed, table foo is near empty so the statements in function bar use seqscans. Then table foo is filled with many rows. Now, when bar is executed again, will PG (8.3.1) know that a seqscan is no longer reasonable?
On Sun, Apr 27, 2008 at 2:06 AM, Matthew Dennis <mdennis@merfer.net> wrote: > Do SQL statements inside of plpgsql functions get planned upon every > execution, only when the function is first executed/defined, or something > else entirely? They are planned on first execution and the plan is cached for later use. -Doug
"Matthew Dennis" <mdennis@merfer.net> writes: > Do SQL statements inside of plpgsql functions get planned upon every > execution, only when the function is first executed/defined, or something > else entirely? First executed per session. > Now, when bar is executed again, will PG (8.3.1) know that a seqscan is no > longer reasonable? It won't notice until someone runs ANALYZE on that table. autovacuum should notice that it's necessary and run but might not run promptly enough for your purposes. You might have to run analyze within your function. Before 8.3 it still wouldn't get replanned until you started a new session but 8.3 is more clever. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!