Re: [SQL] Alternative to INTERSECT
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 + 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
[SQL] Alternative to INTERSECT
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Alternative to INTERSECT
On Tue, 31 Jul 2007 17:30:51 + 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? Josh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alternative to INTERSECT
On Tuesday 31 July 2007 18:52:22 Josh Trutwin wrote: On Tue, 31 Jul 2007 17:30:51 + 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
Re: [SQL] Alternative to INTERSECT
On 7/31/07, Andreas Joseph Krogh [EMAIL PROTECTED] 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%'; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Alternative to INTERSECT
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org