Thread: pgsql: Support index-only scans using the visibility map to avoid heap
Support index-only scans using the visibility map to avoid heap fetches. When a btree index contains all columns required by the query, and the visibility map shows that all tuples on a target heap page are visible-to-all, we don't need to fetch that heap page. This patch depends on the previous patches that made the visibility map reliable. There's a fair amount left to do here, notably trying to figure out a less chintzy way of estimating the cost of an index-only scan, but the core functionality seems ready to commit. Robert Haas and Ibrar Ahmed, with some previous work by Heikki Linnakangas. Branch ------ master Details ------- http://git.postgresql.org/pg/commitdiff/a2822fb9337a21f98ac4ce850bb4145acf47ca27 Modified Files -------------- doc/src/sgml/catalogs.sgml | 7 + doc/src/sgml/config.sgml | 19 ++- doc/src/sgml/indexam.sgml | 32 ++++- doc/src/sgml/ref/postgres-ref.sgml | 7 +- src/backend/access/index/genam.c | 4 + src/backend/access/index/indexam.c | 236 ++++++++++++++++--------- src/backend/access/nbtree/nbtree.c | 92 ++++++++++ src/backend/commands/explain.c | 5 +- src/backend/executor/nodeIndexscan.c | 123 ++++++++++++- src/backend/nodes/copyfuncs.c | 1 + src/backend/nodes/outfuncs.c | 2 + src/backend/optimizer/path/costsize.c | 21 +++ src/backend/optimizer/path/indxpath.c | 89 +++++++++ src/backend/optimizer/plan/createplan.c | 9 +- src/backend/optimizer/plan/planner.c | 2 +- src/backend/optimizer/util/pathnode.c | 8 +- src/backend/optimizer/util/plancat.c | 1 + src/backend/optimizer/util/var.c | 43 +++-- src/backend/tcop/postgres.c | 5 +- src/backend/utils/cache/relcache.c | 4 +- src/backend/utils/misc/guc.c | 9 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/access/genam.h | 3 + src/include/access/relscan.h | 5 + src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_am.h | 52 +++--- src/include/nodes/execnodes.h | 2 + src/include/nodes/plannodes.h | 5 + src/include/nodes/relation.h | 6 + src/include/optimizer/cost.h | 4 +- src/include/optimizer/pathnode.h | 1 + src/include/optimizer/var.h | 2 +- src/test/regress/expected/aggregates.out | 78 ++++---- src/test/regress/expected/rangefuncs.out | 27 ++-- 34 files changed, 704 insertions(+), 203 deletions(-)
Re: pgsql: Support index-only scans using the visibility map to avoid heap
From
Cédric Villemain
Date:
2011/10/8 Tom Lane <tgl@sss.pgh.pa.us>: > Support index-only scans using the visibility map to avoid heap fetches. > > When a btree index contains all columns required by the query, and the > visibility map shows that all tuples on a target heap page are > visible-to-all, we don't need to fetch that heap page. This patch depends > on the previous patches that made the visibility map reliable. > > There's a fair amount left to do here, notably trying to figure out a less > chintzy way of estimating the cost of an index-only scan, but the core > functionality seems ready to commit. It looks like it lacks some if(), by reading costsize.c I didn't see where indexonly is used (so visibility_fraction is always used....) Is it ok ? > > Robert Haas and Ibrar Ahmed, with some previous work by Heikki Linnakangas. > > Branch > ------ > master > > Details > ------- > http://git.postgresql.org/pg/commitdiff/a2822fb9337a21f98ac4ce850bb4145acf47ca27 > > Modified Files > -------------- > doc/src/sgml/catalogs.sgml | 7 + > doc/src/sgml/config.sgml | 19 ++- > doc/src/sgml/indexam.sgml | 32 ++++- > doc/src/sgml/ref/postgres-ref.sgml | 7 +- > src/backend/access/index/genam.c | 4 + > src/backend/access/index/indexam.c | 236 ++++++++++++++++--------- > src/backend/access/nbtree/nbtree.c | 92 ++++++++++ > src/backend/commands/explain.c | 5 +- > src/backend/executor/nodeIndexscan.c | 123 ++++++++++++- > src/backend/nodes/copyfuncs.c | 1 + > src/backend/nodes/outfuncs.c | 2 + > src/backend/optimizer/path/costsize.c | 21 +++ > src/backend/optimizer/path/indxpath.c | 89 +++++++++ > src/backend/optimizer/plan/createplan.c | 9 +- > src/backend/optimizer/plan/planner.c | 2 +- > src/backend/optimizer/util/pathnode.c | 8 +- > src/backend/optimizer/util/plancat.c | 1 + > src/backend/optimizer/util/var.c | 43 +++-- > src/backend/tcop/postgres.c | 5 +- > src/backend/utils/cache/relcache.c | 4 +- > src/backend/utils/misc/guc.c | 9 + > src/backend/utils/misc/postgresql.conf.sample | 1 + > src/include/access/genam.h | 3 + > src/include/access/relscan.h | 5 + > src/include/catalog/catversion.h | 2 +- > src/include/catalog/pg_am.h | 52 +++--- > src/include/nodes/execnodes.h | 2 + > src/include/nodes/plannodes.h | 5 + > src/include/nodes/relation.h | 6 + > src/include/optimizer/cost.h | 4 +- > src/include/optimizer/pathnode.h | 1 + > src/include/optimizer/var.h | 2 +- > src/test/regress/expected/aggregates.out | 78 ++++---- > src/test/regress/expected/rangefuncs.out | 27 ++-- > 34 files changed, 704 insertions(+), 203 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