Database Reference
In-Depth Information
Any scan can be enabled or disabled using the
SET
command.
This
SET
command enables a sequential scan:
SET enable_seqscan = TRUE/FALSE;
This
SET
command enables an index scan:
SET enable_indexscan = TRUE/FALSE;
This
SET
command enables a bitmap scan:
SET enable_bitmapscan = TRUE/FALSE;
This
SET
command enables an index-only scan:
SET enable_indexonlyscan = TRUE/FALSE;
The next scan is automatically selected if the lower cost scan is disabled.
Index scans
An index is a way to eficiently retrieve speciic rows from database. The planner
chooses an index scan if any index satisies the
WHERE
condition. It is faster than
the normal table scan because it does not traverse the whole set of column of rows.
Normally, an index is created on tables with lesser number of columns. In index
scans, PostgreSQL picks only one tuple's pointer and accesses the tuple/row from
the table.
An index based on all columns of table has no performance beneit.
Here is an example of an index scan:
warehouse_db=# EXPLAIN SELECT * FROM record.history WHERE
history_id=1000;
QUERY PLAN
------------------------------------------------------------------
Index Scan using idx on history (cost=0.57..8.59 rows=1
width=46)
Index Cond: (history_id = 1000)
Planning time: 0.142 ms
(3 rows)