Re: SELECT is faster on SQL Server

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.

regards, tom lane