JoeBe JoeBe - 6 months ago 43
SQL Question

postgres fast check if attribute combination also exists in another table

I want to check if the same two attribute values exist in two different tables. If the combination from table_a is not existing in table_b it should be inserted into the select statement table. Right now I have the following query, which is working:

CREATE TABLE table_a (
attr_a integer,
attr_b text,
uuid character varying(200),
CONSTRAINT table_a_pkey PRIMARY KEY (uuid)

CREATE TABLE table_b (
attr_a integer,
attr_b text,
uuid character varying(200),
CONSTRAINT table_b_pkey PRIMARY KEY (uuid)

SELECT * FROM table_a
WHERE (table_a.attr_a::text || table_a.attr_b::text) != ALL(SELECT (table_b.attr_a::text || table_b.attr_a::text) FROM table_b)

However, the execution time is pretty long. So I would like to ask if there is a faster solution to check for that.

Answer Source

Your where clause uses a manipulation of attr_a (casting it to text and concatinating with attr_b), so the index can't be used. Instead of this concatination, why not try a straight-forward exists operator?

FROM   table_a a
                   FROM   table_b b
                   WHERE  a.attr_a = b.attr_a AND
                          b.attr_b = b.attr_b)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download