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