Regex query not using index

From: "Postgres User" <postgres(dot)developer(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Regex query not using index
Date: 2008-02-20 03:32:42
Message-ID: b88c3460802191932i2798352cra622e33a43af729d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm running a simple query on 8.2. With this syntax, Explain indicate
that the index is scanned:
select * from eod where name = 'AA'

However, when I change the query to use simple regex:
select * from eod where name ~ 'AA'

now Explain indicates a seq scan:
Index Scan using equity_eod_symbol_idx on equity_eod (cost=0.00..8.27
rows=1 width=149)
Index Cond: ((symbol)::text = 'AA'::text)

Is there any way to 'encourage' Postgres to hit the index when using
regex? Do I need to create a functional index or something?
Without the index in play, I really can't use regex on any of my larger tables.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hewei 2008-02-20 03:38:50 How to make update rapidly?
Previous Message Geoffrey 2008-02-20 02:23:43 ERROR: relation with OID 1322527 does not exist