sql: support lookup joins into virtual table indexes #48178
Description
Since #47316, some virtual tables now have virtual indexes that support single-span, single-datum constraint lookups. This can be used to support lookup joins, and in fact, doing so is instrumental for reasonable performance for some metadata queries.
This example is from ActiveRecord. It checks foreign keys of a table.
SELECT
t2.oid::REGCLASS::STRING AS to_table,
a1.attname AS column,
a2.attname AS primary_key,
c.conname AS name,
c.confupdtype AS on_update,
c.confdeltype AS on_delete,
c.convalidated AS valid
FROM
pg_constraint AS c
JOIN pg_class AS t1 ON c.conrelid = t1.oid
JOIN pg_class AS t2 ON c.confrelid = t2.oid
JOIN pg_attribute AS a1 ON
(a1.attnum = c.conkey[1]) AND (a1.attrelid = t1.oid)
JOIN pg_attribute AS a2 ON
(a2.attnum = c.confkey[1]) AND (a2.attrelid = t2.oid)
JOIN pg_namespace AS t3 ON c.connamespace = t3.oid
WHERE
((c.contype = 'f') AND (t1.relname = 'family_trees'))
AND (t3.nspname = ANY (current_schemas(false)))
ORDER BY
c.conname;
The explain currently looks like this:
tree | field | description
--------------------------------------------------------------------------------------+--------------------+-------------------------------------------
| distributed | false
| vectorized | false
render | |
└── sort | |
│ | order | +conname
└── render | |
└── hash-join | |
│ | type | inner
│ | equality | (oid) = (connamespace)
├── filter | |
│ │ | filter | nspname = ANY ARRAY['public']
│ └── render | |
│ └── virtual table | |
│ | source | pg_namespace@primary
└── hash-join | |
│ | type | inner
│ | equality | (attrelid, attnum) = (oid, column131)
│ | left cols are key |
├── render | |
│ └── virtual table | |
│ | source | pg_attribute@primary
└── render | |
└── hash-join | |
│ | type | inner
│ | equality | (attrelid, attnum) = (oid, column108)
│ | left cols are key |
│ | right cols are key |
├── render | |
│ └── virtual table | |
│ | source | pg_attribute@primary
└── render | |
└── merge-join | |
│ | type | inner
│ | equality | (oid) = (confrelid)
│ | left cols are key |
│ | mergeJoinOrder | +"(oid=confrelid)"
├── sort | |
│ │ | order | +oid
│ └── render | |
│ └── virtual table | |
│ | source | pg_class@pg_class_oid_idx
└── sort | |
│ | order | +confrelid
└── merge-join | |
│ | type | inner
│ | equality | (conrelid) = (oid)
│ | left cols are key |
│ | right cols are key |
│ | mergeJoinOrder | +"(conrelid=oid)"
├── filter | |
│ │ | filter | contype = 'f'
│ └── sort | |
│ │ | order | +conrelid
│ └── render | |
│ └── virtual table | |
│ | source | pg_constraint@pg_constraint_conrelid_idx
└── filter | |
│ | filter | relname = 'family_trees'
└── sort | |
│ | order | +oid
└── render | |
└── virtual table | |
| source | pg_class@pg_class_oid_idx
Ideally, once we look up the oid at the bottom, we could keep following the chain of oids, doing lookup joins on each table, instead of the merge/hash joins that force materializing the whole set of descriptors in memory.