Partial index with regexp not working

From: "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com>
To: "Postgres General" <pgsql-general(at)postgresql(dot)org>
Subject: Partial index with regexp not working
Date: 2007-09-11 09:47:02
Message-ID: e373d31e0709110247w621524b4ge4249535605e56ed@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a varchar ID field which captures a user account. If there is
no user id, then we just store the IP address of the user. Realizing
that we would like to index only those tuples where the user ID is not
an IP, I am trying to create a partial index as follows:

CREATE INDEX idx_trades_tid_partial ON trades (trader_id)
WHERE trader_id ~ '[a-z]' ;

Any "trader_id" that contains an alphabet letter will be registered
user, as IPs only have numbers and dots. But when I do an EXPLAIN
ANALYZE on a query which has a where condition as follows:

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

TIA

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gregory Stark 2007-09-11 09:48:22 Re: Time Zone design issues
Previous Message hubert depesz lubaczewski 2007-09-11 09:37:04 Re: Alternative to drop index, load data, recreate index?