From a2822fb9337a21f98ac4ce850bb4145acf47ca27 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 7 Oct 2011 20:13:02 -0400 Subject: 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. --- 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 +++++-- 4 files changed, 61 insertions(+), 4 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index d6baf84248e..0495bd03bd5 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -476,6 +476,13 @@ Does the access method support multicolumn indexes? + + amcanreturn + bool + + Can the access method return the contents of index entries? + + amoptionalkey bool diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index fbcd455694b..d3a8b2648d8 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -2404,6 +2404,22 @@ SET ENABLE_SEQSCAN TO OFF; + + enable_indexonlyscan (boolean) + + index-only scan + + + enable_indexonlyscan configuration parameter + + + + Enables or disables the query planner's use of index-only-scan plan + types. The default is on. + + + + enable_material (boolean) @@ -6353,7 +6369,7 @@ LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1) , , , - , , + , , , , @@ -6362,6 +6378,7 @@ LOG: CleanUpLock: deleting: lock(0xb7acd844) id(24688,24696,0,0,0,1) enable_indexscan = off, enable_mergejoin = off, enable_nestloop = off, + enable_indexonlyscan = off, enable_seqscan = off, enable_tidscan = off diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml index bb942583853..724b413755d 100644 --- a/doc/src/sgml/indexam.sgml +++ b/doc/src/sgml/indexam.sgml @@ -134,6 +134,11 @@ amsearchnulls, indicating that it supports IS NULL and IS NOT NULL clauses as search conditions. + An index method can also set amcanreturn, + indicating that it can support index-only scans by returning + the indexed column values for an index entry in the form of an IndexTuple. + (An example of an index AM that cannot do this is hash, which stores only + the hash values not the original data.) @@ -385,6 +390,18 @@ amgettuple (IndexScanDesc scan, callers. + + If the access method supports index-only scans (i.e., + amcanreturn is TRUE in its pg_am + row), then on success it must also check + scan->xs_want_itup, and if that is true it should return + the original indexed data for the index entry, in the form of an + IndexTuple stored at scan->xs_itup. However, + it is permissible for the access method to sometimes fail to provide this + data, in which case it must set scan->xs_itup to NULL. That + will result in a regular heap fetch occurring. + + The amgettuple function need only be provided if the access method supports plain index scans. If it doesn't, the @@ -581,6 +598,15 @@ amrestrpos (IndexScanDesc scan); deleted. + + If the index stores the original indexed data values (and not some lossy + representation of them), it is useful to support index-only scans, in + which the index returns the actual data not just the TID of the heap tuple. + This will only work if the visibility map shows that the TID is on an + all-visible page; else the heap tuple must be visited anyway to check + MVCC visibility. But that is no concern of the access method's. + + Instead of using amgettuple, an index scan can be done with amgetbitmap to fetch all tuples in one call. This can be @@ -593,7 +619,11 @@ amrestrpos (IndexScanDesc scan); supported. Secondly, the tuples are returned in a bitmap which doesn't have any specific ordering, which is why amgetbitmap doesn't take a direction argument. (Ordering operators will never be - supplied for such a scan, either.) Finally, amgetbitmap + supplied for such a scan, either.) + Also, there is no provision for index-only scans with + amgetbitmap, since there is no way to return the contents of + index tuples. + Finally, amgetbitmap does not guarantee any locking of the returned tuples, with implications spelled out in . diff --git a/doc/src/sgml/ref/postgres-ref.sgml b/doc/src/sgml/ref/postgres-ref.sgml index 3807f4090a8..9869a1f5262 100644 --- a/doc/src/sgml/ref/postgres-ref.sgml +++ b/doc/src/sgml/ref/postgres-ref.sgml @@ -376,12 +376,15 @@ PostgreSQL documentation - { s | i | m | n | h } + { s | i | o | b | t | n | m | h } Forbids the use of particular scan and join methods: s and i - disable sequential and index scans respectively, while + disable sequential and index scans respectively, + o, b and t + disable index-only scans, bitmap index scans, and TID scans + respectively, while n, m, and h disable nested-loop, merge and hash joins respectively. -- cgit v1.2.3