Re: Partial index with regexp not working

Richard Broersma Jr wrote:
> --- 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?

That's true if you are trying to match an expression to the index. For
example:
   SELECT * FROM foo WHERE bar LIKE '%whatever%'
A btree index can't help here for the same reason you can't find someone
in a phone-book by their first name.
   SELECT * FROM foo WHERE bar LIKE 'whatever%'
This *can* use an index, but only if you are in "C" locale or have set
up text/varchar_pattern_ops appropriately. Then it gets converted into
 >= 'whatever' < 'whateves'.

Now in Phoenix's example the regexp is just being used to specify what
values the index covers. A more common example might be:
   CREATE INDEX inv_unpaid_idx ON invoices (client_id) WHERE NOT paid;
This indexes client_id but only for those invoices that haven't been
paid. Useful for a late-debtors report perhaps if you have lots of
invoices but 99% have been paid already.

The planner isn't smart enough to figure out which queries can use this
index by examining them, it just looks for (NOT paid) in the WHERE
clause and if it doesn't find it, ignores the index.

--
   Richard Huxton
   Archonet Ltd