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

On Tue, Feb 27, 2018 at 2:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Hadi Moshayedi <hadi(at)citusdata(dot)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.