From: Bruce Momjian <bruce@momjian.us>
To: Peter Geoghegan <peter@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] encouraging index-only scans
Date: Thu, 5 Sep 2013 20:14:37 -0400
Message-ID: <20130906001437.GA29264@momjian.us> (raw)
In-Reply-To: <20130904205655.GR21874@momjian.us>
References: <50C8F1EC.9010801@dunslane.net>
<15321.1355347953@sss.pgh.pa.us>
<50C90154.8060103@dunslane.net>
<50C904DB.9090305@dunslane.net>
<20121213024837.GB22363@momjian.us>
<23298.1355370698@sss.pgh.pa.us>
<CAEYLb_U1dTg=bXkDL=yvFTrMw5oC13ivgZA=jdQXnbgaDJ+e-Q@mail.gmail.com>
<20130904205655.GR21874@momjian.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On Wed, Sep 4, 2013 at 04:56:55PM -0400, Bruce Momjian wrote:
> > "Add a column pg_class.relallvisible to remember the number of pages
> > that were all-visible according to the visibility map as of the last
> > VACUUM
> > (or ANALYZE, or some other operations that update pg_class.relpages).
> > Use relallvisible/relpages, instead of an arbitrary constant, to
> > estimate how many heap page fetches can be avoided during an
> > index-only scan."
> >
> > Have I missed some nuance?
>
> I am looking back at this issue now and I think you are correct. The
> commit you mention (Oct 7 2011) says ANALYZE updates the visibility map,
> and the code matches that:
>
> if (!inh)
> vac_update_relstats(onerel,
> RelationGetNumberOfBlocks(onerel),
> totalrows,
> --> visibilitymap_count(onerel),
> hasindex,
> InvalidTransactionId);
>
> so if an index scan was not being used after an ANALYZE, it isn't a bad
> allvisibile estimate but something else. This code was in PG 9.2.
Actually, I now realize it is more complex than that, and worse. There
are several questions to study to understand when pg_class.relallvisible
is updated (which is used to determine if index-only scans are a good
optimization choice), and when VM all-visible bits are set so heap pages
can be skipped during index-only scans:
1) When are VM bits set:
vacuum (non-full)
analyze (only some random pages)
2) When are massive rows added but VM bits not set:
copy
3) When are VM bits cleared:
insert/update/delete
vacuum (non-full)
4) When are VM map files cleared:
vacuum full
cluster
5) When is pg_class.relallvisible updated via a VM map file scan:
vacuum (non-full)
analyze
Vacuums run by autovacuum are driven by n_dead_tuples, which is only
update and delete. Therefore, any scenario where vacuum (non-full) is
never run will not have significant VM bits set. The only bits that
will be set will be by pages visited randomly by analyze.
The following table activities will not set proper VM bits:
vacuum full
cluster
copy
insert-only
If updates and deletes happen, there will eventually be sufficient
reason for autovacuum to vacuum the table and set proper VM bits, and
pg_class.relallvisible.
The calculus we should use to determine when we need to run vacuum has
changed with index-only scans, and I am not sure we ever fully addressed
this.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bruce@momjian.us, peter@2ndquadrant.com, tgl@sss.pgh.pa.us, andrew@dunslane.net, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] encouraging index-only scans
In-Reply-To: <20130906001437.GA29264@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox