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

Re: Index-only scan performance regression

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Index-only scan performance regression
Date: 2012-02-02 00:44:00
Message-ID: CA+TgmoZxT1uHUn2fOUpdd-XG6bL0T7XT7YfBA=SV2Cj2vstOjA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2012-02-02 00:58:42
Subject: Re: foreign key locks, 2nd attempt
Previous:From: Jim NasbyDate: 2012-02-02 00:33:47
Subject: Re: foreign key locks, 2nd attempt

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