We couldn't get plans for this query, presumably because some parameters are missing: syntax error at or near "then"

[Thigoe]

Hello,

I have an issue which i wrote to the title, i contacted with @rjuju at hypopg repo, he suggested as i quoted below;

I think that the error message is coming from powa-web right? This is because the query text comes from pg_stat_statements, which normalise it and remove all constant values. It then relies on pg_qualstats to get the predicates values, but the constant values in the select part are not predicates and therefore not saved by pg_qualstats. So when powa-web tries to recreate a query to test hypothetical indexes it gets a syntax error.

I think that postgres now supports EXPLAIN with the query provided by pg_stat_statements as is, so maybe powa could try it too as a fallback.

The query which resulted the issue is;

select inlandvoya0_.id as col_0_0_, case when inlandvoya0_.load_district_id=$6 then $7 when inlandvoya0_.arrival_district_id=$8 then $9 when inlandvoya0_.status_id=$10 then $11 when inlandvoya0_.status_id=$12 then $13 when inlandvoya0_.status_id=$14 then $15 when inlandvoya0_.status_id=$16 then $17 end as col_1_0_, array_to_string(array((select distinct coalesce((select translatio2_.value from translation_value translatio2_...REDACTED

Thank you for your efforts and time.

[rjuju]

Hi,

(transferring the issue to powa-web as it's where the problem happens)

Sorry I should have mentioned that I'm also the one maintaining this code too.

After double checking, it's indeed possible to get an explain plan in that situation starting with postgres 16. Which version of postgres are you using?

For reference, this is the new GENERIC_PLAN option in EXPLAIN:

=# SELECT 'test', relname FROM pg_class WHERE relnamespace > 10000000;
 ?column? | relname 
----------+---------
(0 rows)

=# SELECT query FROM pg_stat_statements WHERE query LIKE '%relnamespace >%';
                          query                           
----------------------------------------------------------
 SELECT $1, relname FROM pg_class WHERE relnamespace > $2
(1 row)

=# EXPLAIN SELECT $1, relname FROM pg_class WHERE relnamespace > $2;
ERROR:  42P02: there is no parameter $1
LINE 1: EXPLAIN SELECT $1, relname FROM pg_class WHERE relnamespace ...

=# EXPLAIN (GENERIC_PLAN) SELECT $1, relname FROM pg_class WHERE relnamespace > $2;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..19.19 rows=138 width=96)
   Filter: (relnamespace > $2)
(2 rows)

We could probably always add this option when the server is pg16+.

[Thigoe]

Hello again,

Yes, I am on pg16. I also getting other errors like this, therefore resolving this would be a great progress for the project.