1

I'm trying to understand the motivation of PostgreSQL 12 query planner here.

Database Structure

CREATE TABLE subjects
(
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
);

CREATE TABLE metadata
(
    id SERIAL PRIMARY KEY,
    label TEXT NOT NULL UNIQUE,
);

CREATE TABLE subject_metadata
(
    subject_id INT REFERENCES subjects (id),
    metadata_id INT REFERENCES metadata (id),

    nval BIGINT NULL,
    strval TEXT null,
    values JSONB NULL,

    PRIMARY KEY(subject_id, metadata_id)
);

CREATE INDEX idx_subject_metadata_values ON
   subject_metadata(subject_id, metadata_id, strval, nval);

Data Set

Table subjects contains ~10 million rows, table metadata ~1000 rows and subject_metadata ~400 million rows.

Query

I need to run queries for subject having 1 to N metadata references. So my naive approach was to simply join subject_metadata for each queried metadata:

SELECT d.id, d.name, dmd1.strval, dmd2.nval as lang 
FROM subjects d
  JOIN subject_metadata dmd1 on dmd1.subject_id = d.id
  JOIN subject_metadata dmd2 on dmd2.subject_id = d.id
WHERE d.name > '0' 
  AND (dmd1.metadata_id = 2 AND dmd1.strval = 'us') 
  AND (dmd2.metadata_id = 16 AND dmd2.nval > 100)
ORDER BY d.name
LIMIT 100

This works quite well for two joins with query times < 80ms. Plan: https://explain.depesz.com/s/PgXg

Joining subject_metadata a third time leads to a drastic performance drop. The following query - which only differs from the original by an additional join - takes 43 seconds to complete:

SELECT d.id, d.name, dmd1.strval, dmd2.nval as lang 
FROM subjects d
  JOIN subject_metadata dmd1 on dmd1.subject_id = d.id
  JOIN subject_metadata dmd2 on dmd2.subject_id = d.id
  JOIN subject_metadata dmd3 on dmd3.subject_id = d.id
WHERE d.name > '0' 
  AND (dmd1.metadata_id = 2 AND dmd1.strval = 'us') 
  AND (dmd2.metadata_id = 16 AND dmd2.nval > 100)
  AND dmd3.metadata_id = 605
ORDER BY d.name
LIMIT 100

Plan: https://explain.depesz.com/s/sNrU

Rewriting this query by changing the order of join (joining subjects last), brings the performance in line with the first query:

SELECT d.id, d.name, dmd1.strval, dmd2.nval as lang 
FROM subject_metadata dmd1
  JOIN subject_metadata dmd2 on dmd2.subject_id = dmd1.subject_id
  JOIN subject_metadata dmd3 on dmd3.subject_id = dmd2.subject_id
  JOIN subjects d on dmd3.subject_id = d.id
WHERE d.name > '0' 
  AND (dmd1.metadata_id = 2 AND dmd1.strval = 'us') 
  AND (dmd2.metadata_id = 16 AND dmd2.nval > 100)
  AND dmd3.metadata_id = 605
ORDER BY d.name
LIMIT 100

This version completes in 150ms. Plan: https://explain.depesz.com/s/XEjL

I would like to understand why re-ordering the joins makes such a huge difference and/or if there's even a better way to implement this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.