Re: Seqscan slowness and stored procedures

On 8 June 2012 11:58, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

> 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?

Yes, and yes.

>> 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.

Ok.

>> 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.

Thanks for clearing this up for me! I thought SQL functions are also
pre-planned and that the plans are static.