On Wed, 2026-01-07 at 14:57 -0800, Yuri Kutsko wrote: > I want to identify every query in my company’s database that potentially uses > full table scans. > To do this, I wrote a simple PL/pgSQL function that generates an execution > plan for each query > from pg_stat_statements and then searches the plan for the 'Seq Scan' pattern. > Most queries contain parameters, so I use EXPLAIN (GENERIC_PLAN). This works > for approximately > 80% of queries, but it fails in the following scenarios: > > 1. Planner cannot infer parameter types without explicit casts > The following fails: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE id = $1 + > $2; > The following works: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE id = > $1::integer + $2::integer; > > 2. Parameter is used in EXTRACT > The following fails: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE > EXTRACT($1 FROM date_col) = 1; > The following works: EXPLAIN (GENERIC_PLAN) SELECT * FROM tbl WHERE > date_part($1, date_col) = 1; > > 3. Parameter is used in type 'string' notation > The following fails: EXPLAIN (GENERIC_PLAN) SELECT int $1; > The following works: EXPLAIN (GENERIC_PLAN) SELECT $1::int; > > I modified my procedure to account for these scenarios, but I am not sure > whether there are other > issues with EXPLAIN (GENERIC_PLAN) that I am not aware of. > Problem #1 is mentioned in the EXPLAIN documentation > (https://www.postgresql.org/docs/18/sql-explain.html) at the bottom of the > page, but the other cases are not. > My questions are: > Are all of the issues described above expected behavior for EXPLAIN > (GENERIC_PLAN)?
Yes. Note that your cases #2 and #3 are illegal SQL, since you cannot use a parameter in these places, only string literals. I'd say that your problem is that you are using strings from pg_stat_statements, which ignores the value of constants. Replacing literals with placeholders can result in incorrect SQL statements. > Are there other known scenarios where EXPLAIN (GENERIC_PLAN) will fail? It is expected to fail for all statements with syntax errors... > Should the EXPLAIN documentation be updated to list scenarios in which > EXPLAIN (GENERIC_PLAN) can fail? I don't think that #2 and #3 deserve documentation, and I'd say it doesn't need to be documented that EXPLAIN will fail for syntactically incorrect SQL. Yours, Laurenz Albe
