Indexes are used for two purpose, for selection and for ordering, but EXPLAIN
shows them as 'Index Scan' in both cases. This patch changes EXPLAIN output of
Index Scan without Index Cond to 'Index Full Scan'.
It is for novice DBAs. I found that they said "Ok, this query uses an index",
but that is actually a bad plan; index full scan + merge join. After ANALYZE,
the plan was changed to nested loop + index selection, and performance was
improved. So I want to emphasize non-conditional index scan as index *full* scan.
[Example]
# CREATE TABLE test (j int, k int);
# INSERT INTO test SELECT n, n FROM generate_series(1, 100000) as n;
# ALTER TABLE test ADD PRIMARY KEY (j);
# ANALYZE;
# EXPLAIN SELECT j FROM test WHERE k < 20000 ORDER BY j;
Index Full Scan using test_pkey on test (cost=0.00..2567.00 rows=21192 width=4)
Filter: (k < 20000)
# EXPLAIN SELECT j FROM test WHERE j < 20000 ORDER BY j;
Index Scan using test_pkey on test (cost=0.00..545.86 rows=21192 width=4)
Index Cond: (j < 20000)
---
ITAGAKI Takahiro
NTT OSS Center
