Steven Grimm schrieb am 14.11.2015 um 07:25:
We have a table, call it "multi_id", that contains columns with IDs of various kinds of objects in my system,
and another table that's a generic owner/key/value store for object attributes (think configuration settings,
and I'll refer to this table as "settings"). To wit:
---------------------------------------------
CREATE TABLE multi_id (
id1 INTEGER PRIMARY KEY,
id2 INTEGER,
id3 INTEGER
);
CREATE TABLE settings (
owner_id INTEGER,
setting_id INTEGER,
setting_value TEXT,
PRIMARY KEY (owner_id, setting_id)
);
CREATE UNIQUE INDEX multi_id_idx_id1 ON multi_id (id1, id2);
CREATE UNIQUE INDEX multi_id_idx_id2 ON multi_id (id2, id1);
CREATE INDEX settings_idx_setting_id ON settings (setting_id, setting_value);
---------------------------------------------
We want to find all the rows from multi_id where any of the IDs (including its primary key) have a certain setting with a certain value.
LATERAL seemed like the tool for the job, so we tried the following:
---------------------------------------------
SELECT mid.id1
FROM multi_id AS mid,
LATERAL (
SELECT 1
FROM settings
WHERE setting_id = 1
AND setting_value = 'common_1'
AND owner_id IN (mid.id1, mid.id2, mid.id3)
) AS setting_matcher;
---------------------------------------------
The above is actualy a CROSS JOIN between multi_id and settings which generates duplicate values for id1 and is probably not what you want
I _think_ what you are after is something like this:
with sett as (
SELECT owner_id
FROM settings
WHERE setting_id = 1
AND setting_value = 'common_1'
)
select mid.id1
from multi_id as mid
where exists (SELECT 1
FROM sett
WHERE owner_id = mid.id1)
or exists (SELECT 1
FROM sett
where owner_id = mid.id2)
or exists (SELECT 1
FROM sett
where owner_id = mid.id3);
This returns the same result as your original query (when I apply a DISTINCT on it to remove the duplicate ids).
It runs in 23ms on my computer, your cross join takes roughly 4 seconds.
This is the plan from your statement: http://explain.depesz.com/s/EyjJ
This is the plan for my statement: http://explain.depesz.com/s/Dt7x
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
