[HACKERS] No Index-Only Scan on Partial Index

[David E. Wheeler]

Hackers,

I was trying to figure out why a query was not doing an index-only scan on a 
partial index, when Josh Berkus pointed to this issue, reported by Merlin 
Moncure:

  
http://www.postgresql.org/message-id/CAHyXU0x1OGao48WajAfUsbXqkUDLf=_6ho6hlmb8dsfkwda...@mail.gmail.com

In short, the planner needs the column from the where clause included in the 
index to decide it can do an index-only scan. This test case demonstrates the 
truth of this finding:

    CREATE TABLE try (
        id     INT NOT NULL,
        label  TEXT NOT NULL,
        active BOOLEAN DEFAULT TRUE
    );

    INSERT INTO try
    SELECT i
         ,  (ARRAY['foo','bar','baz','wig'])[floor((random()*4))::int + 1]
         , (i % 100) = 0
      FROM generate_series(1, 100000) i;

    VACUUM FREEZE TRY;

    CREATE INDEX idx_try_active ON try(id) WHERE active;

    -- Does a bitmap heap scan.
    EXPLAIN (ANALYZE, FORMAT YAML)
    SELECT id FROM try WHERE active;

    DROP INDEX idx_try_active;
    CREATE INDEX idx_try_active ON try(label, id, active) WHERE active;

    -- Does an index-only scan.
    EXPLAIN (ANALYZE, FORMAT YAML)
    SELECT id FROM try WHERE active;

    DROP TABLE try;

The first query does a bitmap heap scan, but after the index that includes the 
active column is added, it does an index-only scan.

However, this does not quite match my case. In my case, I'm using an immutable 
function call in the index where clause:

    CREATE INDEX idx_try_active ON try(id, upper_inf(irange)) WHERE 
upper_inf(irange);

I am unable to get the planner do to an index-only scan with this index no 
matter what I do. Here’s the full test case:

    CREATE TABLE try (
        id     INT       NOT NULL,
        label  TEXT      NOT NULL,
        irange INT4RANGE NOT NULL
    );

    INSERT INTO try
    SELECT i
         ,  (ARRAY['foo','bar','baz','wig'])[floor((random()*4))::int + 1]
         , int4range(1, CASE WHEN random() < 0.01 THEN NULL ELSE 2 END)
      FROM generate_series(1, 100000) i;

    VACUUM FREEZE TRY;

    CREATE INDEX idx_try_active ON try(id) WHERE upper_inf(irange);

    -- Does a bitmap heap scan.
    EXPLAIN (ANALYZE, FORMAT YAML)
    SELECT id FROM try WHERE upper_inf(irange);

    DROP INDEX idx_try_active;
    CREATE INDEX idx_try_active ON try(label, id, upper_inf(irange)) WHERE 
upper_inf(irange);

    -- Also does a bitmap heap scan.
    EXPLAIN (ANALYZE, FORMAT YAML)
    SELECT id FROM try WHERE upper_inf(irange);

    DROP TABLE try;

So is there something about using a function in a conditional index that 
prevents index-only scans? Tested on 9.2 and 9.3, BTW.

Thanks,

David

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers