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 

[Greg Rychlewski]

Hey Thiago,

We just made a change to Postgrex that should help with this. Basically we allow for text protocol queries now. So you shouldn't have to make fake parameters. If you do what you were doing before but add `query_type: :text` option to your Ecto call it should just work. But let us know if you run into any problems. 

Btw this change is only in Postgrex main so you would have to point there until we do a release.