Re: LIKE not using indexes (due to locale issue?)

Ow Mun Heng wrote:
> Could it be that it's not able to determine the B2.%.SX in there?
>
> explain select count(*) from d_trr_iw where ast_revision like 'B2.P.SX'
>
> even this will result in a seq_scan.
>
How many values have you got that start with 'B2.'?  If it's more than
about 5% then it's probably quicker to sequential scan anyway.

Since the equals query says there's one row with a P in the middle, what
does this say?

explain select count(*) from d_trr_iw where ast_revision like 'B2.P%.SX'

or pick a value that you know can't exist e.g. if ast_revision can never start with WW then

explain select count(*) from d_trr_iw where ast_revision like 'WW.%.SX'

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au