Re: Regex query not using index

Thanks, my dumb mistake.
I need to perform the equivalent of a WHERE clause OR expression using
regex to match exact strings.

_________________________________________________________________________

this example hits the index:
select * from eod where name ~ '^BA$'

but when I try to add another possible value to the regex, it does a row scan:
select * from eod where name ~ ^BA$|^AA$'

both of these statements return the right results, but the 2nd ignores
the index even though both values are left-anchored.

any workaround- this behavior doesn't seem to make sense

On Feb 19, 2008 8:45 PM, Erik Jones <erik(at)myemma(dot)com> wrote:
>
> On Feb 19, 2008, at 9:32 PM, Postgres User wrote:
>
> > 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.
>
> You need it to be anchored:
>
> select * from eod where name ~ '^AA';
>
> If you're looking to be able to use indexes for searches within a
> string then, for 8.2, you'll need to check out tsearch2.
>
> Erik Jones
>
> DBA | Emma(R)
> erik(at)myemma(dot)com
> 800.595.4401 or 615.292.5888
> 615.292.0777 (fax)
>
> Emma helps organizations everywhere communicate & market in style.
> Visit us online at http://www.myemma.com
>
>
>
>