On 2021-03-19 4:38 PM, Tom Lane wrote:
Frank Millman <frank@xxxxxxxxxxxx> writes:
However, the bizarre thing is that I have simply restored the index to
what it was in the first place. If you look at the table definition in
my original message you can see that all the columns were included in
the index. But the query did not use it as a covering index. Now the
EXPLAIN ANALYSE clearly shows 'Index Only Scan using ar_tots_cover'. I
have no idea what changed.
VACUUM, maybe? Even if there's a covering index, the planner is not
likely to prefer an index-only scan unless it thinks that most of the
table's pages are known all-visible. If they're not, most of the
rows will require heap probes anyway to check row visibility, meaning
that the "index-only" scan's performance degrades to about that of a
regular indexscan.
In this example, since you're fetching such a large fraction of the
table (which the planner is accurately estimating), there's not a lot
of daylight between the estimated costs of seqscan and index-only
scan to begin with. I'm not surprised that it'd prefer the former
if the table isn't recently vacuumed.
It is possible. I know that I *did* vacuum. But I also ran a program to
generate a few hundred additional rows, and I cannot remember if I ran
the vacuum before or after that.
Frank
