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
thisissue, 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-onlyscan.
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
