Thread: Alternative to INTERSECT

Alternative to INTERSECT

From

Andreas Joseph Krogh

Date:

31 July 2007, 18:03:15

Hi all. I have the following schema:

CREATE TABLE test (   id integer NOT NULL,   field character varying NOT NULL,   value character varying NOT NULL
);

ALTER TABLE ONLY test   ADD CONSTRAINT test_id_key UNIQUE (id, field, value);

CREATE INDEX test_like_idx ON test USING btree (id, field, value 
varchar_pattern_ops);

Using INTERSECT I want to retrieve the rows matching (pseudo-code) "firstname 
LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"

on=> SELECT t.id from test t WHERE t.field = 'firstname' AND t.value 
LIKE 'andrea%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value 
LIKE 'jose%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname'  AND t.value 
LIKE 'kro%';id
---- 1
(1 row)

Is there a way to make this more efficient with another construct, or 
INTERSECT the only way to accomplish the desired result?

--
Andreas Joseph Krogh

Re: Alternative to INTERSECT

From

Josh Trutwin

Date:

31 July 2007, 18:59:12

On Tue, 31 Jul 2007 17:30:51 +0000
Andreas Joseph Krogh <andreak@officenet.no> wrote:

> Hi all. I have the following schema:
> 
> CREATE TABLE test (
>     id integer NOT NULL,
>     field character varying NOT NULL,
>     value character varying NOT NULL
> );
> 
> ALTER TABLE ONLY test
>     ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
> 
> CREATE INDEX test_like_idx ON test USING btree (id, field, value 
> varchar_pattern_ops);
> 
> Using INTERSECT I want to retrieve the rows matching (pseudo-code)
> "firstname LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"

Why not:

WHERE (t.field = lastname AND t.value LIKE 'kro%')  OR (t.field = firsname AND (      t.value LIKE 'jose%' OR t.value
LIKE'andrea%')      )
 

Not tested.  If you're having performance problems is probably less
like that the INTERSECT is the problem with all those LIKE's in
there?  Is t.value indexed?

Josh

Re: Alternative to INTERSECT

From

Andreas Joseph Krogh

Date:

31 July 2007, 19:45:26

On Tuesday 31 July 2007 18:52:22 Josh Trutwin wrote:
> On Tue, 31 Jul 2007 17:30:51 +0000
>
> Andreas Joseph Krogh <andreak@officenet.no> wrote:
> > Hi all. I have the following schema:
> >
> > CREATE TABLE test (
> >     id integer NOT NULL,
> >     field character varying NOT NULL,
> >     value character varying NOT NULL
> > );
> >
> > ALTER TABLE ONLY test
> >     ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
> >
> > CREATE INDEX test_like_idx ON test USING btree (id, field, value
> > varchar_pattern_ops);
> >
> > Using INTERSECT I want to retrieve the rows matching (pseudo-code)
> > "firstname LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"
>
> Why not:
>
> WHERE (t.field = lastname AND t.value LIKE 'kro%')
>    OR (t.field = firsname AND (
>        t.value LIKE 'jose%' OR t.value LIKE 'andrea%')
>        )
>
> Not tested.  If you're having performance problems is probably less
> like that the INTERSECT is the problem with all those LIKE's in
> there?  Is t.value indexed?

Yes, as I wrote:

CREATE INDEX test_like_idx ON test USING btree  (id, field, value varchar_pattern_ops);

And I'm observing that it uses that index.

Your query doesn't cut it, let me try to explain what I'm trying to achieve:

Suppose I have the following data:
INSERT INTO test VALUES (1, 'firstname', 'andreas');
INSERT INTO test VALUES (1, 'firstname', 'joseph');
INSERT INTO test VALUES (1, 'lastname', 'krogh');
INSERT INTO test VALUES (2, 'firstname', 'andreas');
INSERT INTO test VALUES (2, 'lastname', 'noname');

The reason for why I use INTERSECT is that I want:

SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
LIKE 'andrea%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
LIKE 'jose%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname'  AND t.value
LIKE 'kro%';

To return only id 1, and the query:

SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
LIKE 'andrea%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
LIKE 'jose%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname'  AND t.value
LIKE 'non%';

To return no rows at all (cause nobydy's name is "andreas joseph noname").

Your suggestion doesn't cover this case.

--
AJK

Re: Alternative to INTERSECT

From

"Rodrigo De León"

Date:

31 July 2007, 20:31:15

On 7/31/07, Andreas Joseph Krogh <andreak@officenet.no> wrote:
> Is there a way to make this more efficient with another construct, or
> INTERSECT the only way to accomplish the desired result?

SELECT f1.ID FROM TEST f1 JOIN TEST f2 ON f1.ID = f2.ID      JOIN TEST f3 ON f2.ID = f3.IDWHERE f1.FIELD = 'firstname'
ANDf1.VALUE LIKE 'andrea%'  AND f2.FIELD = 'firstname'  AND f2.VALUE LIKE 'jose%'  AND f3.FIELD = 'lastname'  AND
f3.VALUELIKE 'kro%';

Re: Alternative to INTERSECT

From

Stephan Szabo

Date:

31 July 2007, 21:09:16

On Tue, 31 Jul 2007, Andreas Joseph Krogh wrote:

> Hi all. I have the following schema:
>
> CREATE TABLE test (
>     id integer NOT NULL,
>     field character varying NOT NULL,
>     value character varying NOT NULL
> );
>
> ALTER TABLE ONLY test
>     ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
>
> CREATE INDEX test_like_idx ON test USING btree (id, field, value
> varchar_pattern_ops);
>
> Using INTERSECT I want to retrieve the rows matching (pseudo-code) "firstname
> LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"
>
> on=> SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
> LIKE 'andrea%'
> INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
> LIKE 'jose%'
> INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname'  AND t.value
> LIKE 'kro%';

Do you want something with only a firstname of jose or a firstname of jose
and something other than andrea (and no others) to match or not? I'd read
the pseudo-code to say yes, but AFAICT the query says no.

In general, some form of self-join would probably work, but the details
depend on exactly what should be returned.

Re: Alternative to INTERSECT

From

"Luiz K. Matsumura"

Date:

02 August 2007, 01:46:14

I don't know if this is more efficient but an alternative can be 
something like this

SELECT t.id
FROM test t JOIN test t2 ON t2.id = t.id  AND t2.field = 'firstname' AND t2.value 
LIKE 'jose%' JOIN test t3 ON t3.id = t2.id AND t3.field = 'lastname'  AND t3.value 
LIKE 'kro%'
WHERE t.field = 'firstname' AND t.value LIKE 'andrea%'

Hope this helps

Andreas Joseph Krogh wrote:
> On Tuesday 31 July 2007 18:52:22 Josh Trutwin wrote:
>   
>> On Tue, 31 Jul 2007 17:30:51 +0000
>>
>> Andreas Joseph Krogh <andreak@officenet.no> wrote:
>>     
>>> Hi all. I have the following schema:
>>>
>>> CREATE TABLE test (
>>>     id integer NOT NULL,
>>>     field character varying NOT NULL,
>>>     value character varying NOT NULL
>>> );
>>>
>>> ALTER TABLE ONLY test
>>>     ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
>>>
>>> CREATE INDEX test_like_idx ON test USING btree (id, field, value
>>> varchar_pattern_ops);
>>>
>>> Using INTERSECT I want to retrieve the rows matching (pseudo-code)
>>> "firstname LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"
>>>       
>> Why not:
>>
>> WHERE (t.field = lastname AND t.value LIKE 'kro%')
>>    OR (t.field = firsname AND (
>>        t.value LIKE 'jose%' OR t.value LIKE 'andrea%')
>>        )
>>
>> Not tested.  If you're having performance problems is probably less
>> like that the INTERSECT is the problem with all those LIKE's in
>> there?  Is t.value indexed?
>>     
>
> Yes, as I wrote:
>
> CREATE INDEX test_like_idx ON test USING btree 
>   (id, field, value varchar_pattern_ops);
>
> And I'm observing that it uses that index.
>
> Your query doesn't cut it, let me try to explain what I'm trying to achieve:
>
> Suppose I have the following data:
> INSERT INTO test VALUES (1, 'firstname', 'andreas');
> INSERT INTO test VALUES (1, 'firstname', 'joseph');
> INSERT INTO test VALUES (1, 'lastname', 'krogh');
> INSERT INTO test VALUES (2, 'firstname', 'andreas');
> INSERT INTO test VALUES (2, 'lastname', 'noname');
>
> The reason for why I use INTERSECT is that I want:
>
> SELECT t.id from test t WHERE t.field = 'firstname' AND t.value 
> LIKE 'andrea%'
> INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value 
> LIKE 'jose%'
> INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname'  AND t.value 
> LIKE 'kro%';
>
> To return only id 1, and the query:
>
> SELECT t.id from test t WHERE t.field = 'firstname' AND t.value 
> LIKE 'andrea%'
> INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value 
> LIKE 'jose%'
> INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname'  AND t.value 
> LIKE 'non%';
>
> To return no rows at all (cause nobydy's name is "andreas joseph noname").
>
> Your suggestion doesn't cover this case.
>
> --
> AJK
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>
>   

-- 
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.