infoschema: REFERENTIAL_CONSTRAINTS MemTableScan does not consume join-derived table_name/constraint_schema filters
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Summary
Queries that join `INFORMATION_SCHEMA.KEY_COLUMN_USAGE`, `TABLE_CONSTRAINTS`, and `REFERENTIAL_CONSTRAINTS` (the pattern used by MySQL Connector/J `DatabaseMetaData.getImportedKeys()`, commonly triggered by DBeaver schema introspection) can take ~1s+ even when the final result is empty / tiny.
Root cause: `TABLE_CONSTRAINTS` / `KEY_COLUMN_USAGE` get `table_name` predicate pushdown into `MemTableScan`, but `REFERENTIAL_CONSTRAINTS` does **not** receive the equivalent join-derived predicates (`R.TABLE_NAME = B.TABLE_NAME`, `R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA`). It therefore performs a full metadata scan over all schemas/tables to materialize foreign keys.
## Minimal reproduce SQL
```sql
-- Optional: create a DB/table with many other tables in the cluster to amplify cost.
-- The target table may even have no foreign keys; the query can still be slow.
EXPLAIN ANALYZE
SELECT
A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,
A.CONSTRAINT_NAME AS FK_NAME,
R.UNIQUE_CONSTRAINT_NAME AS PK_NAME,
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_NAME)
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (
R.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND R.TABLE_NAME = B.TABLE_NAME
AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA
)
WHERE
B.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND A.TABLE_SCHEMA = ''
AND A.TABLE_NAME = ''
AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL
ORDER BY
A.REFERENCED_TABLE_SCHEMA,
A.REFERENCED_TABLE_NAME,
A.ORDINAL_POSITION;
```
## Expected behavior
After equality derivation through the joins:
```
A.TABLE_NAME = '' + USING (... TABLE_NAME) + R.TABLE_NAME = B.TABLE_NAME
and/or R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA
```
with schema predicates TiDB should push table_name / constraint_schema into REFERENTIAL_CONSTRAINTS MemTableScan, similar to TABLE_CONSTRAINTS, so the scan only touches the target table(s).
## Actual behavior
REFERENTIAL_CONSTRAINTS MemTableScan has no extracted predicates in operator info and falls back to listing schemas/tables (ListSchemasAndTables full path), which becomes expensive as the number of tables grows.
Contributor guide
Research direction
Start by running the provided EXPLAIN ANALYZE query and compare predicate extraction for REFERENTIAL_CONSTRAINTS with TABLE_CONSTRAINTS and KEY_COLUMN_USAGE. Trace the REFERENTIAL_CONSTRAINTS MemTableScan and its ListSchemasAndTables full path; done means join-derived table_name and constraint_schema filters reach the scan and the reproduction no longer performs a full metadata scan.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100