Thread: pgsql: Measure the number of all-visible pages for use in index-only sc
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(-)
Re: pgsql: Measure the number of all-visible pages for use in index-only sc
From
Cédric Villemain
Date:
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