Thread: BUG #2953: index scan, feature request

The following bug has been logged online:

Bug reference:      2953
Logged by:          michael
Email address:      miblogic@yahoo.com
PostgreSQL version: 8.2.0
Operating system:   windows 2000
Description:        index scan, feature request
Details:

hi postgresql team,

can these be executed with index seek like what MS SQL does?

select * from account_category
where account_category_full_description <> 'MICHAEL'

this query resolves to sequential scan in postgres.

i saw ms sql, execution plan for the above query would become:

account_category_full_description < 'MICHAEL' or
account_category_full_description > 'MICHAEL'

thus it would utilize index scan

internally the above ms sql code would execute as:


select * from account_category
where account_category_full_description < 'MICHAEL'
or account_category_full_description > 'MICHAEL'


does my example warrant index scan?

thanks,
mike

"michael" <miblogic@yahoo.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.

            regards, tom lane

On Thu, Feb 01, 2007 at 09:17:51PM -0500, Tom Lane wrote:
> "michael" <miblogic@yahoo.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