No Index-Only Scan on Partial Index

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