Thread: No Index-Only Scan on Partial Index

No Index-Only Scan on Partial Index

From

"David E. Wheeler"

Date:

02 October 2013, 01:35:37

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

Re: No Index-Only Scan on Partial Index

From

Merlin Moncure

Date:

02 October 2013, 01:56:25

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

Re: No Index-Only Scan on Partial Index

From

"David E. Wheeler"

Date:

02 October 2013, 02:30:52

On Oct 1, 2013, at 3:56 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

> 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)

Hrm. I get a seq scan for that query:

create index on try(upper_inf(irange));
explain select * from try where upper_inf(irange);                       QUERY PLAN
-----------------------------------------------------------Seq Scan on try  (cost=0.00..1887.00 rows=33333 width=68)
Filter:upper_inf(irange) 

True also if I just select the irange. Is the filter the issue, here?

Best,

David

Re: No Index-Only Scan on Partial Index

From

Merlin Moncure

Date:

02 October 2013, 15:07:18

On Tuesday, October 1, 2013, David E. Wheeler <<a href="mailto:david@justatheory.com">david@justatheory.com</a>>
wrote:<br/>> On Oct 1, 2013, at 3:56 PM, Merlin Moncure <<a
href="mailto:mmoncure@gmail.com">mmoncure@gmail.com</a>>wrote:<br /> ><br />>> I don't think it has
anythingto do with the conditional index -- it's<br />>> the functional based.  For some reason postgres always
wantsto post<br />>> filter (note the filter step below):<br /> >><br />>> postgres=# create index on
try(upper_inf(irange));<br/>>> CREATE INDEX<br />>> Time: 12.001 ms<br />>> postgres=# explain select
*from try where upper_inf(irange);<br />>>                                  QUERY PLAN<br /> >>
-------------------------------------------------------------------------------<br/>>> Index Scan using
try_upper_inf_idxon try  (cost=0.00..9.25 rows=33 width=40)<br />>>   Index Cond: (upper_inf(irange) = true)<br
/>>>   Filter: upper_inf(irange)<br />><br />> Hrm. I get a seq scan for that query:<br />><br />>
createindex on try(upper_inf(irange));<br />> explain select * from try where upper_inf(irange);<br />>          
             QUERY PLAN<br /> > -----------------------------------------------------------<br />>  Seq Scan on
try (cost=0.00..1887.00 rows=33333 width=68)<br />>    Filter: upper_inf(irange)<br />><br />> True also if I
justselect the irange. Is the filter the issue, here?<br /><br />Turn off seq scan...<br /><br />merlin

Re: No Index-Only Scan on Partial Index

From

"David E. Wheeler"

Date:

03 October 2013, 20:21:35

On Oct 2, 2013, at 5:07 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

> > Hrm. I get a seq scan for that query:
> >
> > create index on try(upper_inf(irange));
> > explain select * from try where upper_inf(irange);
> >                         QUERY PLAN
> > -----------------------------------------------------------
> >  Seq Scan on try  (cost=0.00..1887.00 rows=33333 width=68)
> >    Filter: upper_inf(irange)
> >
> > True also if I just select the irange. Is the filter the issue, here?
>
> Turn off seq scan...

That rewards me with a bitmap heap scan:

EXPLAIN select * from try where upper_inf(irange);
                                     QUERY PLAN
--------------------------------------------------------------------------------------Bitmap Heap Scan on try
(cost=935.63..2197.63rows=33333 width=68)  Filter: upper_inf(irange)  ->  Bitmap Index Scan on try_upper_inf_idx
(cost=0.00..927.30rows=50000 width=0)        Index Cond: (upper_inf(irange) = true) 

But anyway, I still don’t understand why, if the function used to store the value is immutable (as upper_inf() is), why
Postgresdoesn't do an index scan. Is this something that could be improved in the planner? 

Thanks,

David

Re: No Index-Only Scan on Partial Index

From

Josh Berkus

Date:

03 October 2013, 20:50:17

David,

> But anyway, I still don’t understand why, if the function used to store the value is immutable (as upper_inf() is),
whyPostgres doesn't do an index scan. Is this something that could be improved in the planner? 

Yes.  This is clearly a TODO.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: No Index-Only Scan on Partial Index

From

"David E. Wheeler"

Date:

03 October 2013, 23:24:50

On Oct 3, 2013, at 10:50 AM, Josh Berkus <josh@agliodbs.com> wrote:

>>
>> But anyway, I still don’t understand why, if the function used to store the value is immutable (as upper_inf() is),
whyPostgres doesn't do an index scan. Is this something that could be improved in the planner? 
>
> Yes.  This is clearly a TODO.

Added it here:
 https://wiki.postgresql.org/wiki/Todo#Optimizer_.2F_Executor

Teach the planner how to better use partial indexes for index-only scans•
http://www.postgresql.org/message-id/25141.1345072858@sss.pgh.pa.us•
http://www.postgresql.org/message-id/79C7D74D-59B0-4D97-A5E5-55553EF299AA@justatheory.com

Best,

David