opt: common 5-way virtual table join takes 45ms to plan #43039
Description
The following simplified query is used frequently by ActiveRecord. It currently takes 30-45ms to plan, which feels excessive, especially since the query is run so frequently. I've simplified the query to remove a bunch of extra cruft that makes it harder to read.
root@:26257/activerecord_unittest> explain(opt) SELECT
attr.attname,
nsp.nspname,
def.adbin
FROM
pg_class AS t
JOIN pg_attribute AS attr ON t.oid = attrelid
JOIN pg_attrdef AS def ON adrelid = attrelid AND adnum = attnum
JOIN pg_constraint AS cons ON conrelid = adrelid AND adnum = conkey[1]
JOIN pg_namespace AS nsp ON t.relnamespace = nsp.oid
WHERE
t.oid = 3;
text
+-----------------------------------------------------------------------------------+
project
└── inner-join (hash)
├── virtual-scan pg_namespace
├── inner-join (hash)
│ ├── project
│ │ ├── virtual-scan pg_constraint
│ │ └── projections
│ │ └── conkey[1]
│ ├── inner-join (hash)
│ │ ├── select
│ │ │ ├── virtual-scan pg_class@pg_class_oid_idx
│ │ │ │ └── constraint: /1: [/3 - /3]
│ │ │ └── filters
│ │ │ └── pg_class.oid = 3
│ │ ├── inner-join (hash)
│ │ │ ├── virtual-scan pg_attrdef
│ │ │ ├── select
│ │ │ │ ├── virtual-scan pg_attribute@pg_attribute_attrelid_idx
│ │ │ │ │ └── constraint: /29: [/3 - /3]
│ │ │ │ └── filters
│ │ │ │ └── attrelid = 3
│ │ │ └── filters
│ │ │ ├── adrelid = attrelid
│ │ │ └── adnum = attnum
│ │ └── filters
│ │ └── pg_class.oid = attrelid
│ └── filters
│ ├── conrelid = adrelid
│ └── adnum = column81
└── filters
└── relnamespace = pg_namespace.oid
(31 rows)
Time: 50.133ms
Is there anything to be done about this? For comparison, Postgres plans the same query in 1-3ms.