Postgres Pro
Downloads
Home   >   mailing lists

Thread: index-only-scan when there is an index on all columns

index-only-scan when there is an index on all columns

From
Hadi Moshayedi
Date:
With the following definition:

create table t1 (a int, b int, c int);
create index on t1 (a,b,c);

I get the following plan for the following query:

postgres=# explain select sum(c) from t1 where a > 0;
                                    QUERY PLAN                                    
-----------------------------------------------------------------------------------
 Aggregate  (cost=29.62..29.63 rows=1 width=8)
   ->  Bitmap Heap Scan on t1  (cost=9.42..27.92 rows=680 width=4)
         Recheck Cond: (a > 0)
         ->  Bitmap Index Scan on t1_a_b_c_idx  (cost=0.00..9.25 rows=680 width=0)
               Index Cond: (a > 0)
(5 rows)

I am wondering why is it not using index-only-scan (which would use the cache better) and instead it does a bitmap scan?

Thanks,
Hadi


Re: index-only-scan when there is an index on all columns

From
Tom Lane
Date:
Hadi Moshayedi <hadi@citusdata.com> writes:
> I am wondering why is it not using index-only-scan (which would use the
> cache better) and instead it does a bitmap scan?

Never experiment on an empty table and assume that the resulting plan
is the same as you'd get on a large table.

In this case, not only don't you have any meaningful amount of data
loaded, but the planner can see that none of the table's pages are
marked all-visible, meaning that the "index-only" scan would degrade
to a regular indexscan, which is how it gets costed.  And on a single-page
table, an indexscan is going to have a hard time beating other
alternatives.

            regards, tom lane

Re: index-only-scan when there is an index on all columns

From
"David G. Johnston"
Date:
On Tue, Feb 27, 2018 at 2:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hadi Moshayedi <hadi@citusdata.com> writes:
> I am wondering why is it not using index-only-scan (which would use the
> cache better) and instead it does a bitmap scan?

Never experiment on an empty table and assume that the resulting plan
is the same as you'd get on a large table.

In this case, not only don't you have any meaningful amount of data
loaded, but the planner can see that none of the table's pages are
marked all-visible, meaning that the "index-only" scan would degrade
to a regular indexscan, which is how it gets costed.  And on a single-page
table, an indexscan is going to have a hard time beating other
alternatives.

If one runs vacuum on a table (small or otherwise) that is currently choosing an index scan as its best plan how likely is it that post-vacuum an index-only plan would be chosen if the index type and column presence conditions are met?

Also, I recall discussion that select statements will touch the visibility map (hence causing write I/O even in a read-only query) but [1] indicates that only vacuum will set them ddl will clear them.

[1] https://www.postgresql.org/docs/10/static/storage-vm.html

David J.

Re: index-only-scan when there is an index on all columns

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> If one runs vacuum on a table (small or otherwise) that is currently
> choosing an index scan as its best plan how likely is it that post-vacuum
> an index-only plan would be chosen if the index type and column presence
> conditions are met?

Offhand I think it would always prefer IOS over regular indexscan if the
table is mostly all-visible.  The problem in this example was that other
choices dominate both.

> Also, I recall discussion that select statements will touch the visibility
> map (hence causing write I/O even in a read-only query) but [1] indicates
> that only vacuum will set them ddl will clear them.

Hm, I don't recall that, but I've not been involved in the last few rounds
of hacking on that mechanism.

            regards, tom lane

Re: index-only-scan when there is an index on all columns

From
"David G. Johnston"
Date:
On Tue, Feb 27, 2018 at 2:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
 
> Also, I recall discussion that select statements will touch the visibility
> map (hence causing write I/O even in a read-only query) but [1] indicates
> that only vacuum will set them ddl will clear them.

Hm, I don't recall that, but I've not been involved in the last few rounds
of hacking on that mechanism.

​I was confusing hint bits with the visibility map, sorry for the noise and/or confusion.

David J.
​

Re: index-only-scan when there is an index on all columns

From
Andres Freund
Date:
On 2018-02-27 16:58:11 -0500, Tom Lane wrote:
> > Also, I recall discussion that select statements will touch the visibility
> > map (hence causing write I/O even in a read-only query) but [1] indicates
> > that only vacuum will set them ddl will clear them.
> 
> Hm, I don't recall that, but I've not been involved in the last few rounds
> of hacking on that mechanism.

I'm fairly certain that only vacuum and table rewrites like cluster sets
all-visible, and that there was never any released code that did so
during SELECT. I think there were a few patches debating whether we
could change that, but they never really got anywhere afair.

Greetings,

Andres Freund

Re: index-only-scan when there is an index on all columns

From
David Rowley
Date:
On 28 February 2018 at 11:11, Andres Freund <andres@anarazel.de> wrote:
> I'm fairly certain that only vacuum and table rewrites like cluster sets
> all-visible,

I don't think the pages are set all visible again after a rewrite.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: index-only-scan when there is an index on all columns

From
Andres Freund
Date:
On 2018-02-28 13:15:45 +1300, David Rowley wrote:
> On 28 February 2018 at 11:11, Andres Freund <andres@anarazel.de> wrote:
> > I'm fairly certain that only vacuum and table rewrites like cluster sets
> > all-visible,
> 
> I don't think the pages are set all visible again after a rewrite.

You're right. We freeze the tuples, but don't set the heap / FSM bits.

Greetings,

Andres Freund