From: Luiz K. Matsumura <luiz@planit.com.br>
To: Andreas Joseph Krogh <andreak@officenet.no>
Cc: pgsql-sql@postgresql.org
Subject: Re: Alternative to INTERSECT
Date: Wed, 01 Aug 2007 22:46:00 -0300
Message-ID: <46B13758.9000204@planit.com.br> (raw)
In-Reply-To: <200707311945.16573.andreak@officenet.no>
References: <200707311730.51280.andreak@officenet.no>
<20070731135222.394da3dd@sinkhole.intrcomm.net>
<200707311945.16573.andreak@officenet.no>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: luiz@planit.com.br, andreak@officenet.no
Subject: Re: Alternative to INTERSECT
In-Reply-To: <46B13758.9000204@planit.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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