Re: Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

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