Re: Index-only scan performance regression

On Wed, Feb 1, 2012 at 4:09 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>> The real objection to this probably is that if it only saves anything
>> for tables that don't have a VM yet, it's dubious whether it's worth
>> doing.  But if we can avoid wasted checks for VM extension as well,
>> then I think it's probably a no-brainer.
>
> Yes it applies in the same way to VM extension - if the table has
> grown and the VM has not yet been extended, but I don't see why that
> is any worse than the case of not having a VM yet.
>
> Actually I think that this is not such an uncommon case - for a table
> which has only had data inserted - no deletes or updates - it is
> tempting to think that ANALYSE is sufficient, and that there is no
> need to VACUUM. If it were simply the case that this caused an
> index-only scan to have no real benefit, you might be willing to live
> with normal index scan performance. But actually it causes a very
> significant performance regression beyond that, to well below 9.1
> performance.

So, I guess the trade-off here is that, since sinval messages aren't
processed immediately, we often won't notice the VM extension until
the next statement starts, whereas with the current implementation, we
notice it right away. On the other hand, noticing it right away is
costing us a system call or two per tuple. So on further thought, I
think we should do this.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company