Re: Partial index with regexp not working

--- Richard Huxton <dev@archonet.com> wrote:
> Phoenix Kiula wrote:
> > CREATE INDEX idx_trades_tid_partial ON trades (trader_id)
> >     WHERE trader_id ~ '[a-z]' ;
>
> >    WHERE trader_id = 'johndoe'
> >
> > It is not using this index at all! It is using no index in fact, it's
> > trying to do a sequential scan. Any ideas why this partial index is
> > not working??
> A partial index will only be considered if you test for its condition:
>
> SELECT ... WHERE trader_id = 'johndoe' AND trader_id ~ '[a-z]'

IIRC, for any index like this to work, doesn't the REGEXP need to be anchored to either the start
or end of the string?

Regards,
Richard Broersma Jr.