Support EXPLAIN (GENERIC_PLAN) without providing parameters

[Thiago Santos]

Hello, 

PostgreSQL 16 introduced EXPLAIN (GENERIC_PLAN), which allows you to see the execution plan the database would use for a parameterized query without knowing the specific values.

That is really helpful when analyzing traces where a slow query is flagged and only a parameterized version is available. 

Today Ecto.Adapters.SQL.query requires us to pass dummy values even when trying to check a GENERIC_PLAN for a parameterized query.

I would love if we could explain those raw SQL queries using Ecto. Right now I need to use psql or provide dummy values for Ecto.Adapters.SQL.query. The goal behind this is to create a livebook where our team can past those queries coming from honeycomb and easily get a GENERIC_PLAN for it.

That is something that makes sense for ecto? What would be the best place to add it? A new explain function or support for parameterized queries without passing the parameters on Ecto.Adapters.SQL.query?

Thanks

[Greg Rychlewski]

I think this one is tough because the issue is actually at a lower level, the Postgrex driver. It uses a protocol to talk to Postgres that is heavily influenced by the needs of prepared statements. So a lot of the guts of the driver will assume you have the right number of parameters.

I can think of one work around for you. You don't really need Ecto for this since you are using raw SQL. So what you can do is start a simple connection in Postgres and issue the query that way: https://hexdocs.pm/postgrex/Postgrex.SimpleConnection.html#content

This uses the same protocol as psql