Re: Index-only scan performance regression

On Wed, Feb 1, 2012 at 7:44 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.

Patch committed. I moved the smgr inval to after the actual extension
is done, which seems superior, and adjusted the comments slightly.

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