Getting explain plans

[yhuelf]

Hi, What does mean this error? We couldn't get plans for this query, presumably because some parameters are missing: syntax error at or near ")"

I'm not sure too understand what powa is supposed to do, since the queries in pg_stat_statement are normalized.

The related logs are below (anonymized with '_____') :

2021-09-14 11:58:06 UTC [1099]: db=XXX,user=powa,app=[unknown],client=::1 ERROR:  syntax error at or near ")" at character 54
2021-09-14 11:58:06 UTC [1099]: db=XXX,user=powa,app=[unknown],client=::1 STATEMENT:  EXPLAIN SELECT "a"."id", SUM((COALESCE("c"."_____", ?) - COALESCE("c"."_____", ?))) FROM "_____" AS "a" INNER JOIN "_____" AS "b" ON (("b"."left" >= "a"."left") AND ("b"."right" <= "a"."right")) INNER JOIN "_____" AS "c" ON ("c"."_____" = "b"."id") WHERE (((("a"."id" >= ?) AND ("a"."id" <= ?)) OR (("a"."id" >= ?) AND ("a"."id" <= ?)) OR (("a"."id" >= ?) AND ("a"."id" <= ?)) OR (("a"."id" >= ?) AND ("a"."id" <= ?)) OR ("a"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))) AND ("c"."move" IN (SELECT "d"."id" FROM "_____" AS "d" LEFT JOIN "_____" AS "e" ON ("d"."period" = "e"."id") LEFT JOIN "_____" AS "f" ON ("e"."_____" = "f"."id") WHERE (((? AND ("d"."state" = ?)) AND ("f"."id" = ?)) AND ("d"."date" <= ?::date))))) GROUP BY "a"."id"

[anayrat]

Hello,

If I am right, the UI takes predicates from pg_qualstat to generate EXPLAIN query. So if pg_qualstats.sample_rate is too low or if your workload generates too many predicates, it could happen predicates are missing to generate the EXPLAIN query.

[yhuelf]

OK, it does make sense, thank you for this explanation!

[rjuju]

Indeed, but the problem here is different.

The error message is actually complaining about a coalesce(something, ?), like:

 select coalesce(null, ?);
ERROR:  42601: syntax error at or near ")"
LINE 1: select coalesce(null, ?);

You have constants in the SELECT part of your statements, and as those aren't quals they're not stored by pg_qualstats. So when the UI tries to get a complete query string to run an explain, it first tries to get it from the non-normalized queries stored by pg_qualstats, but in your case it's not available anymore (as per previous issue). If then fallback to getting a normalized query and try to regenerate one using the quals, and end up with an invalid query as the constants in the SELECT part aren't available anywhere.

This is a known limitation unfortunately.

[yhuelf]

OK, now I understand better how PoWA works, thank you!