Re: Seqscan slowness and stored procedures

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