EXPLAIN(GENERIC_PLAN) failing for some queries
| От | Yuri Kutsko |
|---|---|
| Тема | EXPLAIN(GENERIC_PLAN) failing for some queries |
| Дата | |
| Msg-id | CABvjvhruTCeDfx6XMoG5xEY74tJHip1zxHny6TFXEruL4=sKsQ@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: EXPLAIN(GENERIC_PLAN) failing for some queries
|
| Список | pgsql-general |
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)?
Are there other known scenarios where EXPLAIN (GENERIC_PLAN) will fail?
Should the EXPLAIN documentation be updated to list scenarios in which EXPLAIN (GENERIC_PLAN) can fail?
Yuri Kutsko
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)?
Are there other known scenarios where EXPLAIN (GENERIC_PLAN) will fail?
Should the EXPLAIN documentation be updated to list scenarios in which EXPLAIN (GENERIC_PLAN) can fail?
Yuri Kutsko
В списке pgsql-general по дате отправления: