Regex query not using index

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®
erik@myemma.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

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

Postgres User wrote:

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

try changing it to

select * from eod where (name ~ '^BA$' or name ~ '^AA$')

though in this example they should both be name = 'XX' rather than regex'es.

--
Postgresql & php tutorials
http://www.designmagick.com/

Yes that works, but the whole point of the exercise is replace many OR
statements with 1 regex expression. So it's not what I'm looking for.

Postgres User wrote:

Yes that works, but the whole point of the exercise is replace many OR
statements with 1 regex expression. So it's not what I'm looking for.

Why do you want it done this way?

You can build an array of strings to check and use an in clause.

Using php :

$checks = array('AA', 'BA');

$query = "select * from table where name in ('" . implode("','",
$checks) . "')";

and it should use an index (up to a point anyway).

--
Postgresql & php tutorials
http://www.designmagick.com/

im trying to allow the client to pass a varchar param into my
function, and want to avoid any parsing of the parameter inside the
function, or code to build a sql string.

if the function can use this code, it will be compiled and optimized
(unlike a dynamic sql stirng)

select * from mytable where fielda ~ p_param

(where p_param is the input parameter)

"Postgres User" <postgres.developer@gmail.com> writes:

Yes that works, but the whole point of the exercise is replace many OR
statements with 1 regex expression. So it's not what I'm looking for.

Unfortunately, Postgres is not as intelligent as you are. There is
no mechanism to rewrite a multi-branch regex condition into multiple
indexscans. I recommend going back to the OR's.

regards, tom lane

doh! tom, let me know if you decide to hack out a fix for this one of
these nights ;)
thanks for your help.

by the way, your example works fine unless it's a null value or empty string
unfortunately, postgres isn't smart enough to know that the when
p_param below is null, that the WHERE condition can be ignored

select * from table where name in (Coalesce(p_param, name))

which is the same as: select * from table where name in (name)

postgres does a row scan on the above sql. too slow.

Postgres User wrote:

by the way, your example works fine unless it's a null value or empty string
unfortunately, postgres isn't smart enough to know that the when
p_param below is null, that the WHERE condition can be ignored

select * from table where name in (Coalesce(p_param, name))

which is the same as: select * from table where name in (name)

postgres does a row scan on the above sql. too slow.

If there's no where condition, postgres has to do a seq-scan anyway so
your argument is void :)

--
Postgresql & php tutorials
http://www.designmagick.com/

a final question: why does this syntax do a seq scan + filter:

select * from tablea where fielda = fielda -or- select * from
tablea where fielda in (fielda)

while this syntax results in no filter, seq scan only

select * from tablea where 1 = 1

it seems that both where clauses should be ignored by the optimizer-
or am i missing something

On Wed, Feb 20, 2008 at 12:56:54AM -0800, Postgres User wrote:

a final question: why does this syntax do a seq scan + filter:

select * from tablea where fielda = fielda -or- select * from
tablea where fielda in (fielda)

while this syntax results in no filter, seq scan only

select * from tablea where 1 = 1

it seems that both where clauses should be ignored by the optimizer-
or am i missing something

WHERE fielda = fielda will only match non-null rows...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Postgres User wrote:

im trying to allow the client to pass a varchar param into my
function, and want to avoid any parsing of the parameter inside the
function, or code to build a sql string.

if the function can use this code, it will be compiled and optimized
(unlike a dynamic sql stirng)

select * from mytable where fielda ~ p_param

No, you should never let users specify raw regex. at best they can
hog down your server. Regex is a state engine and you can create
endless loops.

Maybe we can see the overall picture of your query?

Regards
Tino

Tino,

My users are developers and the goal was to accept a simple
comma-delimited list of string values as a function's input parameter.
The function would then parse this input param into a valid regex
expression.

I was trying to write a function that lets me avoid using Execute
<string> and instead write in-line SQL with all the benefits of
pre-compilation and optimization.

Regex offers such a technique- IF it could understand regex that
represented a set of logical ORs and do an index scan (my rule is to
avoid seq-scans)

An example of regex that allows you to use in-line SQL with a
condition equivalent to many OR conditions when using basic comparison
operators:

select * from table1
where name ~ '.*' '^Smith$' |^Jones$':

And this works very well- except for the seq scan instead of an index scan

"Postgres User" <postgres.developer@gmail.com> writes:

My users are developers and the goal was to accept a simple
comma-delimited list of string values as a function's input parameter.
The function would then parse this input param into a valid regex
expression.

Why are you fixated on this being a regex? If you aren't actually
trying to expose regex capabilities to the users, you'll just be having
to suppress a bunch of strange behaviors for special characters.

ISTM that the best solution is to use an array-of-text parameter,
along the lines of

where name = any (array['Smith', 'Jones', ...])

For what you're doing, you'd not actually want the array[] syntax,
it would look more like

where name = any ('{Smith,Jones}'::text[])

This should optimize into an indexscan in 8.2 or later.

regards, tom lane

Tom,

I was looking for another approach but didn't come across that array
syntax in my searches (perhaps because it's newer. Thanks for a
solution.

Now to end my fixation, one last item. What about the case of a null
or empty param value- is there a way to assign a condition value that
Postgres will ignore when processing the query?

This syntax results in a seq scan: WHERE fielda = Coalesce(param, fielda)
because it applies only to non-nulls

Is there another way to write this- perhaps using your array syntax on
an empty array? Basically I'd PG to ignore the condition just as it
ignores WHERE 1 = 1

On Feb 20, 2008, at 5:51 PM, Postgres User wrote:

Now to end my fixation, one last item. What about the case of a null
or empty param value- is there a way to assign a condition value that
Postgres will ignore when processing the query?

This syntax results in a seq scan: WHERE fielda = Coalesce(param,
fielda)
because it applies only to non-nulls

Is there another way to write this- perhaps using your array syntax on
an empty array? Basically I'd PG to ignore the condition just as it
ignores WHERE 1 = 1

Not sure whether you want no rows returned if param is NULL or all
rows (all rows looking at your example), but you could simply check
param for NULL before comparing it, so either:

WHERE param IS NOT NULL AND fielda = param

or

WHERE param IS NULL OR fielda = param

In the second case, if param IS NULL you will get a sequential scan
of course, as that's the most efficient way to return all rows.

On Wed, Feb 20, 2008 at 8:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Postgres User" <postgres.developer@gmail.com> writes:

My users are developers and the goal was to accept a simple
comma-delimited list of string values as a function's input
parameter.
The function would then parse this input param into a valid regex
expression.

Why are you fixated on this being a regex? If you aren't actually
trying to expose regex capabilities to the users, you'll just be
having
to suppress a bunch of strange behaviors for special characters.

ISTM that the best solution is to use an array-of-text parameter,
along the lines of

where name = any (array['Smith', 'Jones', ...])

For what you're doing, you'd not actually want the array[] syntax,
it would look more like

where name = any ('{Smith,Jones}'::text[])

This should optimize into an indexscan in 8.2 or later.

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,47c1522f233091890169212!