Consider this snippet:

create table tbl_1(name text);
create table tbl_2(name text);

create function my_insert(_name text) returns text
    language sql
as $$
   insert into tbl_2(name)
   values (_name);
   select _name;

explain analyse
with x as (select name from tbl_1)
   ,y as (select my_insert(x.name) from x)
select 0


Result  (cost=12.72..12.72 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  CTE x
    ->  Seq Scan on tbl_1  (cost=0.00..12.72 rows=1360 width=32) (never executed)
Planning Time: 0.053 ms 
Execution Time: 0.013 ms

Now I can understand why the clause x isn't being executed (and also y of course), but the plan doesn't mention anything about what happened to clause y.

This leads me to suspect that the query parser is emitting a query that doesn't contain the clause y.

How can I confirm this? How can I see what the parser is emitting here? Alternatively, how can see what query the planner evaluated?

  • If x was never executed, y cannot have been executed, because it uses x. – Laurenz Albe Aug 7 at 14:32

This leads me to suspect that the query parser is emitting a query that doesn't contain the clause y.

The query parser emits query trees and the query planner emits plans. Both can be shown in more detail with the debug_print_parse and debug_print_plan settings. From the documentation:

Now what is a query tree? It is an internal representation of an SQL statement where the single parts that it is built from are stored separately. These query trees can be shown in the server log if you set the configuration parameters debug_print_parse, debug_print_rewritten, or debug_print_plan

In the case of the query in the question, y is apparently eliminated. It's the work of the planner, not of the parser. The log output with debug_print_parse would show it, because that's before it gets eliminated, contrary to the output with debug_print_plan, in which it should not be present.

However, the result of EXPLAIN with no mention of the evaluation of y should be a sufficient proof that the planner has eliminated it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.