pg_lake_table: FOR UPDATE DML picks a bad join plan in GetTableDataFilesHashFromCatalog under stale catalog stats

[sfc-gh-okalaci]

Problem

GetTableDataFilesHashFromCatalog (pg_lake_table/src/fdw/data_files_catalog.c) can pick a catastrophic join plan once catalog tables are large, causing any FOR UPDATE-planned DML on an Iceberg table (e.g. an UPDATE/DELETE with a WHERE clause) to stall for a long time instead of completing in milliseconds.

Root cause

Any DML where the Iceberg table is the result relation goes through CreateTableScanForRelation(..., isResultRelation=true) → GetTableDataFilesFromCatalog(..., forUpdate=true), which appends FOR UPDATE to the internal catalog query joining lake_table.files against data_file_partition_values/partition_fields and data_file_column_stats/field_id_mappings/pg_attribute.

If lake_table.field_id_mappings has stale statistics (in my case n_live_tup = 0 against a real 17,010 rows — autovacuum hadn't caught up under heavy relation churn), the planner picks a plan where field_id_mappings is seq-scanned with no filter at all, feeding a nested loop the planner itself estimated at 42,698 rows, versus the ~95 actually needed for one relation. That cost scales with the size of the whole catalog (all tracked relations' field mappings combined), not the one relation being touched — so it gets worse as more relations/columns accumulate, and can stall outright at scale.

Confirmed via EXPLAIN (ANALYZE, BUFFERS): with stale stats, bad plan (Seq Scan on field_id_mappings, nested loop estimated at 42,698 rows). Running

ANALYZE lake_table.files, lake_table.data_file_column_stats,
        lake_table.data_file_partition_values, lake_table.field_id_mappings,
        lake_table.partition_fields;

immediately restores the good plan (per-relation index scans).

Suggested fix

pg_lake_table already has a mechanism for exactly this class of problem — EnsureFreshStatsForCommitTimeDiff explicitly ANALYZEs lake_table.files/data_file_column_stats before the commit-time diff. Extending that same treatment to field_id_mappings/partition_fields, or otherwise not depending on autovacuum keeping pace for this specific FOR UPDATE read path, would remove this failure mode.

Context

Found while load-testing #511 at scale (many relations, heavy insert load). Unrelated to that PR's own change — it doesn't touch this code path.