Regular Expression for 'and' instead of 'or'

En un mensaje anterior, Samuel J. Sutjiono escribi�:

This expression matches the word socks or shoes or nike in product category
where productdescr ~* '(socks|shoes|nike)'

Does anybody know what the expression should be if I want to do 'and' of those key words instead of 'or' ?

You have to use something like:

'(socks.*shoes.*nike)|(socks.*nike.*shoes)|...'

where ... is every other possible combination. Not pretty, but that's
a limitation of finite automata (or regular expressions, which are
the same).

Regards.

Fernando P. Schapachnik
Gerente de tecnolog�a de red
y sistemas de informaci�n
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381

Thank you very much Fernando. Do you know whether this expression (regex)
use index scan ?

Regards,
Sam
----- Original Message -----
From: "Fernando Schapachnik" <fschapachnik@vianetworks.com.ar>
To: "Samuel J. Sutjiono" <ssutjiono@wc-group.com>
Cc: <pgsql-sql@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Friday, February 22, 2002 1:15 PM
Subject: Re: [SQL] [GENERAL] Regular Expression for 'and' instead of 'or'

En un mensaje anterior, Samuel J. Sutjiono escribi�:

This expression matches the word socks or shoes or nike in product

category

where productdescr ~* '(socks|shoes|nike)'

Does anybody know what the expression should be if I want to do 'and'

of those key words instead of 'or' ?

En un mensaje anterior, Samuel J. Sutjiono escribi�:

Thank you very much Fernando. Do you know whether this expression (regex)
use index scan ?

Not sure, but probably an EXPLAIN will tell.

Good luck.

Fernando P. Schapachnik
Gerente de tecnolog�a de red
y sistemas de informaci�n
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381

Fernando Schapachnik <fschapachnik@vianetworks.com.ar> writes:

You have to use something like:

'(socks.*shoes.*nike)|(socks.*nike.*shoes)|...'

where ... is every other possible combination. Not pretty, but that's
a limitation of finite automata (or regular expressions, which are
the same).

Seems a lot easier to do

WHERE field ~* 'socks' AND field ~* 'shoes' AND field ~* 'nike'

regards, tom lane

Samuel J. Sutjiono writes:

This expression matches the word socks or shoes or nike in product category
where productdescr ~* '(socks|shoes|nike)'

Does anybody know what the expression should be if I want to do 'and'
of those key words instead of 'or' ?

You probably want something like

productdescr ~* 'socks' AND productdescr ~* 'shoes' AND productdescr ~* 'nike'

I don't think it's possible to do this for the general case with just a
regular expression.

--
Peter Eisentraut peter_e@gmx.net

Based on generic rules of Regular Expression, the default is a AND.
For example

RE-1RE-2RE-3 is really (RE-1) and (RE-2) and (RE-3). However Regular
Expression
also provides the "Alternation Metachar" to change it from a default AND
to a OR.

The problem however is if you have target-1 junk target-2 junk junk
target-3
Then the simple (target-1target-2target-3) would not work, as you are
implying
they are following each other. So the fix could be

productdescr ~* '(socks)' AND productdescr ~* '(shoes)' AND productdescr
~*(nike)'

Effectively you are creating a composite expression consisting of three
sub-expression.

"Samuel J. Sutjiono" wrote:

This expression matches the word socks or shoes or nike in product
categorywhere productdescr ~* '(socks|shoes|nike)' Does anybody know
what the expression should be if I want to do 'and' of those key
words instead of 'or' ? Thanks,Sam

--
-------------------------------------------------------------------------
Medi Montaseri medi@CyberShell.com
Unix Distributed Systems Engineer HTTP://www.CyberShell.com
CyberShell Engineering
-------------------------------------------------------------------------

How about using the contrib/tsearch module
that uses gist indexes.

moreover it has builtin support for word stemming,morphology
and stuff. (to my knowledge)

you would ofcourse require to upgrade to PGSQL7.2

excrepts from README.tsearch contrib module.
================
and now you can search all titles with words 'patch' and 'gist':
select title from titles where titleidx ## 'patch&gist';

Here, ## is a new operation defined for type 'txtidx' which could use
index
(if exists) built on titleidx. This operator uses morphology to
expand query, i.e.
## 'patches&gist' will find titles with 'patch' and 'gist' also.
If you want to provide query as is, use operator @@ instead:
select title from titles where titleidx @@ 'patch&gist';

"Samuel J. Sutjiono" wrote:

I'm having an interesting time trying to figure out some behavior with
postgresql indexes that I am trying to understand.

I have a table, historyticket. In that table, I have a column,
fk_opener_id, which is an integer column. I have an index (default btree
index) on the fk_opener_id column in that table.

When I select from this table with the following query:

select * from historyticket where fk_opener_id = ?

The query sometimes uses the index, and sometimes does a sequential scan.
I experimented for a little bit and found out that if the number of rows
that match the query is greater than a certain number (somewhere around
1000 rows from what I can tell) then the index is not used.

For example:

=============
testing=# explain select * from historyticket where fk_opener_id = 67;
NOTICE: QUERY PLAN:

Seq Scan on historyticket (cost=0.00..768.62 rows=1246 width=419)

testing=# select count(*) from historyticket where fk_opener_id = 67;
count
-------
1158
(1 row)
---------
testing=# explain select * from historyticket where fk_opener_id = 4;
NOTICE: QUERY PLAN:

Index Scan using fk_opener_id on historyticket (cost=0.00..179.47 rows=47
width=419)

testing=# select count(*) from historyticket where fk_opener_id = 4;
count
-------
79
(1 row)
===============

I did more queries and confirmed that when the number of rows returned is
below a certain number (I don't have enough data to determine the exact
number) the index is used, and when it is above a certain number, it is
not used.

Can anyone explain to me what is happening / why it is happening / how to
make the indexes work correctly?

Thanks.

Brian

On Wed, Mar 13, 2002 at 04:28:00PM -0500, Brian Knox wrote:

I'm having an interesting time trying to figure out some behavior with
postgresql indexes that I am trying to understand.

[snip]

I did more queries and confirmed that when the number of rows returned is
below a certain number (I don't have enough data to determine the exact
number) the index is used, and when it is above a certain number, it is
not used.

Can anyone explain to me what is happening / why it is happening / how to
make the indexes work correctly?

Well, checking a tuple from an index is more expensive than checking a tuple
from a sequential scan. So, if you want to select 50% of the table, it's
faster to read the whole table than it is to use the index.

The planner tries to guess where the break-even point is. Above, seq scan,
below index scan.

This is a FAQ, IIRC.

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/