Skip to content

perf(catalog): ERD neighbourhood and MCP describe/sample read the whole schema catalog; filter tables in SQL #1572

Open

Description

@ZhuchkaTriplesix

Problem

Two paths read the whole schema catalog when they need a few tables:

  1. ERD neighbourhood (ErdCatalog.loadNeighbourhood, lib/core/erd/erd_catalog.dart:198-248). It loads all FKs (needed for the graph walk), then for every schema in the neighbourhood runs columnsSql for all columns of that schema and filters in Dart (if (names.contains(r[0])), :230/:238). The PostgreSQL columnsSql has per-column correlated subqueries (unique-index EXISTS, pg_enum string_agg, col_description, pg_get_expr), so the cost grows with the schema, not with the diagram. With catalogRowLimit = 200000 (:19) a large schema is truncated before filtering, so the focused table can lose its columns even though it needs only a few rows.
  2. MCP describe_table / sample_rows (McpQueryService._loadCatalog, lib/core/mcp/mcp_query_service.dart:245-270). To find one table it runs three catalog queries over the whole schema (columns, FKs, view columns) on every call. sample_rows uses the result only to validate the table name.

Fix

  • Add a table filter to columnsSql / viewColumnsSql: PostgreSQL c.relname = ANY($names) per schema, MySQL table_name IN (...), SQLite m.name IN (...) (literal-escaped as in _lit, or bind parameters where the delegate supports them).
  • ERD neighbourhood: walk FKs first, then fetch columns only for the resulting names.
  • MCP: validate the name with a cheap existence query (to_regclass / information_schema.tables / sqlite_master), and describe one table with the filtered column/FK queries. Keep the full catalog only for list_tables and the schema:// resource.
  • Tests: the generated SQL contains the filter; the neighbourhood of a table in a schema larger than the row limit is complete.

Related: ERD epic #1166 and #1167 (same catalog SQL).

Activity

  1. added
    performanceTheme parser epic label: performance
    coreCore library logic and services
    backendBackend database driver execution and queries
    P3Low priority / Polish & Enhancements
    on Oct 13, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    P3Low priority / Polish & EnhancementsbackendBackend database driver execution and queriescoreCore library logic and servicesperformanceTheme parser epic label: performance

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions