pgsql: Measure the number of all-visible pages for use in index-only sc

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Measure the number of all-visible pages for use in index-only sc
Date: 2011-10-14 21:24:01
Message-ID: E1REpEX-0004tj-Nu@gemulon.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Measure the number of all-visible pages for use in index-only scan costing.

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.

This is pretty primitive and will no doubt see refinements once we've
acquired more field experience with the index-only scan mechanism, but
it's way better than using a constant.

Note: I had to adjust an underspecified query in the window.sql regression
test, because it was changing answers when the plan changed to use an
index-only scan. Some of the adjacent tests perhaps should be adjusted
as well, but I didn't do that here.

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/e6858e665731c0f56d3ecc9fbb245c32d24f8ef7

Modified Files
--------------
doc/src/sgml/catalogs.sgml | 13 ++++++
src/backend/access/hash/hash.c | 3 +-
src/backend/access/heap/visibilitymap.c | 68 +++++++++++++++++++++++++++++++
src/backend/catalog/heap.c | 4 ++
src/backend/catalog/index.c | 52 ++++++++++++++++-------
src/backend/commands/analyze.c | 11 ++++-
src/backend/commands/cluster.c | 5 ++
src/backend/commands/vacuum.c | 6 +++
src/backend/commands/vacuumlazy.c | 20 ++++++++-
src/backend/nodes/outfuncs.c | 1 +
src/backend/optimizer/path/costsize.c | 18 ++++-----
src/backend/optimizer/util/plancat.c | 30 ++++++++++++-
src/backend/optimizer/util/relnode.c | 2 +
src/backend/utils/cache/relcache.c | 2 +
src/include/access/visibilitymap.h | 3 +-
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_class.h | 45 +++++++++++---------
src/include/commands/vacuum.h | 1 +
src/include/nodes/relation.h | 4 +-
src/include/optimizer/plancat.h | 2 +-
src/test/regress/expected/window.out | 23 +++++-----
src/test/regress/sql/window.sql | 3 +-
22 files changed, 246 insertions(+), 72 deletions(-)

Responses

Browse pgsql-committers by date

  From Date Subject
Next Message Tom Lane 2011-10-15 00:25:32 pgsql: Fix bugs in information_schema.referential_constraints view.
Previous Message Robert Haas 2011-10-14 18:16:29 pgsql: Dump all roles first, then all config settings on roles.