Add execution plan for Postgres

[evernat]

It would be great to have execution plan for Postgres like for Oracle.
It seems to me that the explain command needs to have values instead of binded parameters ('?'). But I suppose that we could replace all '?' by null before the explain command.
So how do we get the execution plan for Postgres given a jdbc connection?

[StefanPenndorf]

Postgres is really smart creating optimized execution plans. Postgres makes fair use of statistics. Thus I would take caution replacing all ? with null. This hit me once when I tried to "debug" a performance problem. I filled in nonsense-values on a test system and the execution plan was fine. Everything should have been blazing fast.
I had to execute the EXPLAIN on the live system using real values in order to see that under those circumstances a missing index slowed down the query. I added this index and everything was fast again.
Long story short: I think you need real values and no nulls in order to get reliable execution plans.

[dhpagani]

I don't have much knowledge about how melody gets the queries, but if has it with the parameters, the strategy i think should work is:
1- if I don't have the explain plan, get it from the first query
2 - if i have it and it still valid (maybe set 1 day or 6 hours, parametrized)
3- if its invalid, get a new one
Maybe an option to force clear the explain plan.

Sorry for bad english

[StefanPenndorf]

Since PostgreSQL 16 there is the option to request the generic execution plan.

See this blog https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/ and the official documentation. Please note that like the documentation states GENERIC_PLAN is not compatible with ANALYZE. Thus no real timings are available because PostgreSQL has no values to fill in to actually execute the query. Perhaps a case distinction is necessary to be able to evaluate queries without parameters with the duration of the queries and parameterised queries with a generic plan without a concrete duration.

[evernat]

done for Postgresql 16 or later by #1223 and merged

[evernat]

merged to 1.x branch also