Thread: BUG #5113: Postgres not scanning indexes

The following bug has been logged online:

Bug reference:      5113
Logged by:          dan
Email address:      dan.boeriu@roost.com
PostgreSQL version: 8.4.1
Operating system:   redhat 5.3
Description:        Postgres not scanning indexes
Details:

Let's say I have a table t with 5 columns c1 NOT NULL, c2 NOT NULL, c3, c4,
c5
and I have a UNIQUE index on (c1, c2) (remember c1 and c2 have a not null
constraint)

When I run the query:
select c1,c2 from t

I expect the explain to say index scan; instead it says table scan.
The index has ALL the info I need and in my case is about 2% the size of the
table. Why is Postgres still choosing to do a full table scan? The same
applies to a primary key - if (c1, c2) is the primary key then I expect:
select c1,c2 from t
to scan only the index - instead it scans the table

On Oct 13, 2009, at 18:46 , dan wrote:

> When I run the query:
> select c1,c2 from t
>
> I expect the explain to say index scan; instead it says table scan.

You're asking for the entire table: why perform the extra work of
using an index and then looking up entries in the table rather than
reading from the table directly? Compare trying to read an entire book
by looking up individual words in the index and then turning to the
appropriate page.

Michael Glaesemann
grzm seespotcode net

On Tue, 13 Oct 2009, dan wrote:

> Let's say I have a table t with 5 columns c1 NOT NULL, c2 NOT NULL, c3, c4,
> c5
> and I have a UNIQUE index on (c1, c2) (remember c1 and c2 have a not null
> constraint)
>
> When I run the query:
> select c1,c2 from t
>
> I expect the explain to say index scan; instead it says table scan.
> The index has ALL the info I need and in my case is about 2% the size of the
> table.

The index is missing the visibility information, so right now the system
cannot run such a query over the index alone since it couldn't determine
if the row version is visible to your statement. That data is currently in
with the data in the table, so it ends up needing to go back and read the
rows from the main table. IIRC, there's been talk and some work around
supporting indexes with visibility info, but I think there might have been
some issues that needed to be worked out before that would work.

>>>>> "dan" == "dan"  <dan.boeriu@roost.com> writes:

 dan> I expect the explain to say index scan; instead it says table scan.
 dan> The index has ALL the info I need

It may have all the info _you_ need, but what it doesn't have is all the
info that _postgres_ needs; specifically it doesn't contain enough row
visibility info for index-only scans to be possible without consulting
the table.

--
Andrew (irc:RhodiumToad)