Demonstration of LATERAL performance issue on PostgreSQL 9.4

slow-lateral.py

#!/usr/bin/env python3
print("""

DROP TABLE settings;
DROP TABLE multi_id;

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)
);

""")

for owner_id in range(0, 5000):
    base = owner_id * 3 + 1
    print("INSERT INTO multi_id VALUES (%d, %d, %d);" % (base, base + 1, base + 2))

    if owner_id < 4500:
        prefix = 'common_'
    else:
        prefix = 'rare_'

    for setting_id in range(1, 50):
        print("INSERT INTO settings VALUES (%d, %d, '%s');" %
                (owner_id, setting_id, prefix + str(setting_id)))

print("""

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);

------
-- The slow form of the query
------
EXPLAIN ANALYZE
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;

------
-- Same output, ~300x faster
------
EXPLAIN ANALYZE
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 = mid.id1
) AS setting_matcher
UNION
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 = mid.id2
) AS setting_matcher
UNION
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 = mid.id1
) AS setting_matcher;

""")