Feature suggestion: equivalent of PostgreSQL 18 pg_restore_relation_stats()

(1) By Simon Willison (simonw) on 2026-03-09 14:29:27 [link] [source]

I just read this fascinating article about the new pg_restore_relation_stats() and pg_restore_attribute_stats() functions that were added in PostgreSQL 18 (released in September 2025):

https://boringsql.com/posts/portable-stats/

These functions solve the problem where your production environment has different query planner statistics from your smaller development environment and hence the query planner makes different decisions

You can now imitate the production statistics locally to help with testing. I liked this example showing how to set column statistics for a status column that is 95% "delivered".

SELECT pg_restore_attribute_stats(
    'schemaname', 'public',
    'relname', 'test_orders',
    'attname', 'status',
    'inherited', false::boolean,
    'null_frac', 0.0::real,
    'avg_width', 9::integer,
    'n_distinct', 5::real,
    'most_common_vals', '{delivered,shipped,cancelled,pending,returned}'::text,
    'most_common_freqs', '{0.95,0.015,0.015,0.015,0.005}'::real[]
);

I wonder if SQLite would benefit from a similar feature.

(2) By Richard Hipp (drh) on 2026-03-09 14:40:30 in reply to 1 [source]

All of the data statistics used by the query planner in SQLite are available in the sqlite_stat1 table (or also in the sqlite_stat4 table if you happen to have compiled with SQLITE_ENABLE_STAT4). That table is writable. You can inject whatever alternative statistics you like.

This approach to controlling the query planner is mentioned in the documentation: https://sqlite.org/optoverview.html#manual_control_of_query_plans_using_sqlite_stat_tables.

The ".fullschema" command in the CLI outputs both the schema and the content of the sqlite_statN tables, exactly for the reasons outlined above - so that we can reproduce query problems for testing without have to load multi-terabyte database files.

(3) By Simon Willison (simonw) on 2026-03-09 15:09:51 in reply to 2 [link] [source]

Amazing, thanks very much.