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@xxxxxxxxxx> 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@xxxxxxxxxx > 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 > > > > ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
