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 <[EMAIL PROTECTED]> 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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
