Production query plans without production data

46 points by sjamaan

simonw

This article is great.

I didn't know that PostgreSQL 18 added these features - the ability to export the query planner affecting statistics and load them into a smaller test database such that the query planner makes the same decisions as production - and they're clearly explained here.

This feels like a very PostgreSQL feature to me - powerful, cleanly designed and pragmatically addressing an issue that affects large scale production workloads.

zimpenfish

Now we need someone with influence to prod the SQLite team to add similar functionality...
- simonw
  
  Good idea, worth a shot.
  - zimpenfish
    
    And ... it turns out that you can already do that with SQLite. Bloody hell. But thanks for posing the question, at least now we know!
    
    "All of the data statistics used by the query planner in SQLite are available in the sqlite_stat1 table [which] is writable. You can inject whatever alternative statistics you like."
    
    simonw
    
    Hah, classic SQLite! Made for a good blog post.
    
    radim
    
    OP here - this is amazing. Going to try it later this week. Btw, I just figured out that I can do more on PostgreSQL using hook get_relation_info_hook - that allows to override even that last remaining issue - so now I can experimentally flip the plan even with 1 row in table.
    
    Will release the extension to allow this as part of RegreSQL
    
    jhd3
    
    Cool - ~~Greenplum~~ Cloudberry has a tool called minirepro that did this. I saw it used by the team that was writing the new (at the time) cost based optimizer to chase down customer reported issues.