Re: BUG #2953: index scan, feature request

On Thu, Feb 01, 2007 at 09:17:51PM -0500, Tom Lane wrote:
> "michael" <miblogic(at)yahoo(dot)com> writes:
> > can these be executed with index seek like what MS SQL does?
> > select * from account_category
> > where account_category_full_description <> 'MICHAEL'
>
> What for? A query like that is generally going to fetch the majority of
> the table, so an indexscan would be counterproductive.
>
> It could potentially be a win if a very large fraction of the rows had
> the exact value MICHAEL ... but the recommended way to deal with that is
> to create a partial index with "full_description <> 'MICHAEL'" as the
> WHERE clause.

Just FYI, the reason that MSSQL does this is most likely that you have a
covering, clustered index on that column. First of all, if you have a
clustered index on that table, SQLServer wil always do an indexscan -
because there is no way to heap-scan such a table. And second, since
SQLServer has covering indexes, they can use indexes in cases where it
returns even a significant portion of the table.

//Magnus