Database Reference
In-Depth Information
Index-only scans
If all the columns of a query are part of the index, then the planner selects index-only
scans. In this case, the tuple on the page is visible, so tuples are picked from an index
instead of a heap, which is a really big performance boost. The following example
shows an index-only scan:
warehouse_db=# EXPLAIN SELECT history_id FROM record.history WHERE
history_id = 1000;
QUERY PLAN
------------------------------------------------------------------
Index Only Scan using idx on history (cost=0.57..8.59 rows=1
width=4)
Index Cond: (history_id = 1000)
Planning time: 0.121 ms
(3 rows)
In the preceding example, all the columns in the target and qual lists are part of the
index; therefore, the planner selects index-only scans.
If an index becomes ineficient or blotted, then the
index must be rebuilt using the
REINDEX
command.
Bitmap scans
Unlike the index scan, the bitmap scan fetches all the tuple-pointers from the disks
and fetches the tuple using the bitmap data structure. A bitmap scan is useful only
when small numbers of rows are needed. Here is an example of a bitmap scan in
which we get rows that have
hist_id
as
1000
or
20000
:
warehouse_db=# EXPLAIN SELECT * FROM record.history WHERE
history_id = 1000 AND history_id = 20000;
QUERY PLAN
------------------------------------------------------------------
Result (cost=4.58..8.59 rows=1 width=46)
One-Time Filter: false
-> Bitmap Heap Scan on history (cost=4.58..8.59 rows=1
width=46)
Recheck Cond: (history_id = 1000)
-> Bitmap Index Scan on idx (cost=0.00..4.58 rows=1
width=0)
Index Cond: (history_id = 1000)
Planning time: 0.191 ms
(7 rows)
