Re: 'Index Full Scan' for Index Scan without Index Cond

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

On Tue, 2006-06-06 at 18:51 +0900, ITAGAKI Takahiro wrote:
> 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'.

I like it.

Will that test always hold true? Even if it is currently true, perhaps
there should be a code comment to say we rely on that behaviour later to
make EXPLAIN function correctly? We definitely need some code comments
somewhere.

Perhaps we should call it 'Ordered Scan' so that it is completely
different from 'Index Scan'?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> 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'.

This'll break existing tools that examine EXPLAIN output (eg, pgAdmin);
I don't really think it does enough for readability to justify that.
IMHO it'd be better to just add a paragraph to the documentation
pointing out what an indexscan without index condition means.

regards, tom lane

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> Perhaps we should call it 'Ordered Scan' so that it is completely
> different from 'Index Scan'?

It's not an either/or proposition; the planner could be using the index
for both selectivity and ordering. This discussion is also overlooking
the possibility that a partial index is being used for its predicate
(again, possibly in combination with explicit index quals and/or
interest in the sort order).

regards, tom lane

On Tue, 2006-06-06 at 10:31 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > Perhaps we should call it 'Ordered Scan' so that it is completely
> > different from 'Index Scan'?
>
> It's not an either/or proposition; the planner could be using the index
> for both selectivity and ordering.

Good point, but Itagaki's aim was to look at the ordering-only
situation, which I think would benefit from separate wording.

> This discussion is also overlooking
> the possibility that a partial index is being used for its predicate
> (again, possibly in combination with explicit index quals and/or
> interest in the sort order).

Assuming its possible, of course. I wasn't able to verify that code just
by eyeballing it, so it doesn't surprise me if you say there are issues.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> On Tue, 2006-06-06 at 10:31 -0400, Tom Lane wrote:
>> This discussion is also overlooking
>> the possibility that a partial index is being used for its predicate

> Assuming its possible, of course.

Sure:

regression=# create index tenk1p on tenk1(unique1) where unique2 % 100 = 0;
CREATE INDEX
regression=# explain select * from tenk1 where unique2 % 100 = 0;
QUERY PLAN
----------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.10..139.14 rows=50 width=244)
Recheck Cond: ((unique2 % 100) = 0)
-> Bitmap Index Scan on tenk1p (cost=0.00..2.10 rows=50 width=0)
(3 rows)

regards, tom lane