No Index-Only Scan on Partial Index
От | David E. Wheeler |
---|---|
Тема | No Index-Only Scan on Partial Index |
Дата | |
Msg-id | 79C7D74D-59B0-4D97-A5E5-55553EF299AA@justatheory.com обсуждение исходный текст |
Ответы |
Re: No Index-Only Scan on Partial Index
|
Список | 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 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
В списке pgsql-hackers по дате отправления: