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