pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAlternative to INTERSECT
6+ messages / 5 participants
[nested] [flat]
* Alternative to INTERSECT
@ 2007-07-31 17:30 Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 3 replies; 6+ messages in thread
From: Andreas Joseph Krogh @ 2007-07-31 17:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Alternative to INTERSECT
@ 2007-07-31 18:52 Josh Trutwin <josh@trutwins.homeip.net>
parent: Andreas Joseph Krogh <andreak@officenet.no>
2 siblings, 1 reply; 6+ messages in thread
From: Josh Trutwin @ 2007-07-31 18:52 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Alternative to INTERSECT
@ 2007-07-31 19:45 Andreas Joseph Krogh <andreak@officenet.no>
parent: Josh Trutwin <josh@trutwins.homeip.net>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Joseph Krogh @ 2007-07-31 19:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Alternative to INTERSECT
@ 2007-07-31 20:30 Rodrigo De León <rdeleonp@gmail.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
2 siblings, 0 replies; 6+ messages in thread
From: Rodrigo De León @ 2007-07-31 20:30 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
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.ID
WHERE f1.FIELD = 'firstname'
AND f1.VALUE LIKE 'andrea%'
AND f2.FIELD = 'firstname'
AND f2.VALUE LIKE 'jose%'
AND f3.FIELD = 'lastname'
AND f3.VALUE LIKE 'kro%';
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Alternative to INTERSECT
@ 2007-07-31 21:09 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
2 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2007-07-31 21:09 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Alternative to INTERSECT
@ 2007-08-02 01:46 Luiz K. Matsumura <luiz@planit.com.br>
parent: Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 0 replies; 6+ messages in thread
From: Luiz K. Matsumura @ 2007-08-02 01:46 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-08-02 01:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-31 17:30 Alternative to INTERSECT Andreas Joseph Krogh <andreak@officenet.no>
2007-07-31 18:52 ` Josh Trutwin <josh@trutwins.homeip.net>
2007-07-31 19:45 ` Andreas Joseph Krogh <andreak@officenet.no>
2007-08-02 01:46 ` Luiz K. Matsumura <luiz@planit.com.br>
2007-07-31 20:30 ` Rodrigo De León <rdeleonp@gmail.com>
2007-07-31 21:09 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox