Re: No Index-Only Scan on Partial Index

On Tue, Oct 1, 2013 at 5:35 PM, David E. Wheeler <david(at)justatheory(dot)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 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.

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