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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
