Re: No Index-Only Scan on Partial Index - Mailing list pgsql-hackers

On Tue, Oct 1, 2013 at 5:35 PM, David E. Wheeler <david@justatheory.com> wrote:
> 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
tothis 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-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. 

I don't think it has anything to do with the conditional index -- it's
the functional based.  For some reason postgres always wants to post
filter (note the filter step below):

postgres=# create index on try(upper_inf(irange));
CREATE INDEX
Time: 12.001 ms
postgres=# explain select * from try where upper_inf(irange);                                 QUERY PLAN
-------------------------------------------------------------------------------Index Scan using try_upper_inf_idx on
try (cost=0.00..9.25 rows=33 width=40)  Index Cond: (upper_inf(irange) = true)  Filter: upper_inf(irange) 

merlin