Re: SELECT is faster on SQL Server

Frank Millman <frank@chagford.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