Re: Partial index with regexp not working

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??

Why would it? There's no magic that lets PG know one arbitrary condition
correlates with another.

A partial index will only be considered if you test for its condition:

SELECT ... WHERE trader_id = 'johndoe' AND trader_id ~ '[a-z]'

For searching for specific IDs I wouldn't expect a partial index to be
much better than a full index - unless you have a *lot* of IP addresses
and hardly any names.

--
Richard Huxton
Archonet Ltd