Skip site navigation (1) Skip section navigation (2)

No Index-Only Scan on Partial Index

From: "David E(dot) Wheeler" <david(at)justatheory(dot)com>
To: pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: No Index-Only Scan on Partial Index
Date: 2013-10-01 22:35:25
Message-ID: 79C7D74D-59B0-4D97-A5E5-55553EF299AA@justatheory.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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=_6ho6hLMB8DSfkWdaVVw@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

Responses

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2013-10-01 22:56:19
Subject: Re: No Index-Only Scan on Partial Index
Previous:From: Oskari SaarenmaaDate: 2013-10-01 22:31:08
Subject: [PATCH] pg_upgrade: support for btrfs copy-on-write clones

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group