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

2011/10/14 Tom Lane <tgl@sss.pgh.pa.us>:

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.

Fun! it is exactly what I proposed to handle cache estimate and to be
able to refine the costsize based on that. (idea is a (1.0 - var) in
first stage, like you did).
Comments ( http://archives.postgresql.org/message-id/BANLkTikO-M0eBqg0AXynZ+Dq0gdNWUvcbQ@mail.gmail.com
) are valid here, except point 3. (and maybe not so clear that we
*need* a new column to store visibility estimate, we may just use a
mecanism like autovacuum)

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(-)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation