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

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

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]https://www.postgresql.org/docs/10/static/storage-vm.html indicates
that only vacuum will set them ddl will clear them.

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

David J.

"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

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.
​

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

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

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