Re: SELECT is faster on SQL Server

On 2021-03-19 4:38 PM, Tom Lane wrote:
> Frank Millman <frank(at)chagford(dot)com> 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