Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




48.3. Index Scanning

In an index scan, the index access method is responsible for regurgitating the TIDs of all the tuples it has been told about that match the scan keys. The access method is not involved in actually fetching those tuples from the index's parent table, nor in determining whether they pass the scan's time qualification test or other conditions.

A scan key is the internal representation of a WHERE clause of the form index_key operator constant , where the index key is one of the columns of the index and the operator is one of the members of the operator class associated with that index column. An index scan has zero or more scan keys, which are implicitly ANDed — the returned tuples are expected to satisfy all the indicated conditions.

The operator class may indicate that the index is lossy for a particular operator; this implies that the index scan will return all the entries that pass the scan key, plus possibly additional entries that do not. The core system's index-scan machinery will then apply that operator again to the heap tuple to verify whether or not it really should be selected. For non-lossy operators, the index scan must return exactly the set of matching entries, as there is no recheck.

Note that it is entirely up to the access method to ensure that it correctly finds all and only the entries passing all the given scan keys. Also, the core system will simply hand off all the WHERE clauses that match the index keys and operator classes, without any semantic analysis to determine whether they are redundant or contradictory. As an example, given WHERE x > 4 AND x > 14 where x is a b-tree indexed column, it is left to the b-tree amrescan function to realize that the first scan key is redundant and can be discarded. The extent of preprocessing needed during amrescan will depend on the extent to which the index access method needs to reduce the scan keys to a "normalized" form.

The amgettuple function has a direction argument, which can be either ForwardScanDirection (the normal case) or BackwardScanDirection. If the first call after amrescan specifies BackwardScanDirection, then the set of matching index entries is to be scanned back-to-front rather than in the normal front-to-back direction, so amgettuple must return the last matching tuple in the index, rather than the first one as it normally would. (This will only occur for access methods that advertise they support ordered scans by setting pg_am.amorderstrategy nonzero.) After the first call, amgettuple must be prepared to advance the scan in either direction from the most recently returned entry.

The access method must support "marking" a position in a scan and later returning to the marked position. The same position may be restored multiple times. However, only one position need be remembered per scan; a new ammarkpos call overrides the previously marked position.

Both the scan position and the mark position (if any) must be maintained consistently in the face of concurrent insertions or deletions in the index. It is OK if a freshly-inserted entry is not returned by a scan that would have found the entry if it had existed when the scan started, or for the scan to return such an entry upon rescanning or backing up even though it had not been returned the first time through. Similarly, a concurrent delete may or may not be reflected in the results of a scan. What is important is that insertions or deletions not cause the scan to miss or multiply return entries that were not themselves being inserted or deleted. (For an index type that does not set pg_am.amconcurrent, it is sufficient to handle these cases for insertions or deletions performed by the same backend that's doing the scan. But when amconcurrent is true, insertions or deletions from other backends must be handled as well.)

Instead of using amgettuple, an index scan can be done with amgetmulti to fetch multiple tuples per call. This can be noticeably more efficient than amgettuple because it allows avoiding lock/unlock cycles within the access method. In principle amgetmulti should have the same effects as repeated amgettuple calls, but we impose several restrictions to simplify matters. In the first place, amgetmulti does not take a direction argument, and therefore it does not support backwards scan nor intrascan reversal of direction. The access method need not support marking or restoring scan positions during an amgetmulti scan, either. (These restrictions cost little since it would be difficult to use these features in an amgetmulti scan anyway: adjusting the caller's buffered list of TIDs would be complex.) Finally, amgetmulti does not guarantee any locking of the returned tuples, with implications spelled out in Section 48.4.

  Published courtesy of The PostgreSQL Global Development Group Design by Interspire