Ivan Voras wrote: > I have a SQL function (which I've pasted below) and while testing its > code directly (outside a function), this is the "normal", default plan: > > http://explain.depesz.com/s/vfP (67 ms) > > and this is the plain with enable_seqscan turned off: > > http://explain.depesz.com/s/EFP (27 ms) > > Disabling seqscan results in almost 2.5x faster execution. > > However, when this code is wrapped in a function, the execution time is > closer to the second case (which is great, I'm not complaining): > > edem=> explain analyze select * from document_content_top_voted(36); > QUERY PLAN > ------------------------------------------------------------------------ ------------------------------ > ----------------------------- > Function Scan on document_content_top_voted (cost=0.25..10.25 > rows=1000 width=188) (actual time=20.644..20.821 rows=167 loops=1) > Total runtime: 21.236 ms > (2 rows) > > I assume that the difference between the function execution time and the > direct plan with seqscan disabled is due to SQL parsing and planning. That cannot be, because SQL functions do not cache execution plans. Did you take caching of table data in the buffer cache or the filesystem cache into account? Did you run your tests several times in a row and were the actual execution times consistent? > Since the plan is compiled-in for stored procedures, is the planner in > that case already running under the assumption that seqscans must be > disabled (or something to that effect)? > > Would tweaking enable_seqscan and other planner functions during the > CREATE FUNCTION have an effect on the stored plan? No, but you can use the SET clause of CREATE FUNCTION to change enable_seqscan for this function if you know that this is the right thing. But be aware that things might be different for other function arguments or when the table data change, so this is normally considered a bad idea. > Do the functions need to be re-created when the database is fully > populated, to adjust their stored plans with regards to new selectivity > situation on the indexes? No. Even in PL/pgSQL, where plans are cached, this is only for the lifetime of the database session. The plan is generated when the function is called for the first time in a database session. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
