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
witha 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
probablynot 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