Skip site navigation (1) Skip section navigation (2)

Re: Seqscan slowness and stored procedures

From: Ivan Voras <ivoras(at)freebsd(dot)org>
To: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Seqscan slowness and stored procedures
Date: 2012-06-08 11:00:11
Message-ID: CAF-QHFUMbHoJhdHT8trC17VzPCvpW0hs45K+3ymUM2reY=651Q@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

pgsql-performance by date

Next:From: Guillaume CottenceauDate: 2012-06-08 11:11:11
Subject: non index use on LIKE on a non pattern string
Previous:From: Cédric VillemainDate: 2012-06-08 10:59:53
Subject: Re: Multiple Concurrent Updates of Shared Resource Counter

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group