CTE Execution Order

Question

In the following CTE statement:

CREATE TABLE test_table (Field1 INTEGER, Field2 INTEGER);
CREATE TABLE test_table2 (Field1 INTEGER, Field2 INTEGER);

WITH table_stage1(fld1, fld2) AS
    (SELECT Field1, Field2 from test_table1)
, table_stage2 AS 
    (SELECT fld1, fld2 FROM table_stage1 GROUP BY fld1, fld2)
, table_stage3 AS 
    (SELECT fld1 FROM table_stage1 GROUP BY fld1)
INSERT INTO test_table2(Field1, Field2)
    SELECT t1.fld1, t2.fld2
        FROM table_stage2 t1
        JOIN table_stage3 t2
    ON t1.fld1 = t2.fld1;

Can I assume the following order of query execution:

1. SELECT inside WITH statemt
2. Concurrent execution of SELECT inside table_stage2, and table_stage3 segments
3. INSERT INTO waits till execution of table_stage2, table_stage3 is finished

This question is not related to a particular (presented) statement.

I would like to know if by selecting from a named segment there is a guarantee the current statement will be executed after the particular named segment. Meybe what is significat is having a number of select statements folowed by a write CTE that joins results from previous segments

The PostgeSQL documentations we can read:

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable.

I am working on PostgreSQL 9.6

Answer 1

The part of the documentation that you cite refers to the execution of more than one data-modifying statement (using data-modifying statements ... the order is unpredictable). But if in one of the statement is used the name of a previous statement, this means that the current statement refer to all the tuples returned by the previous one.

So, in your example the statements relative to table_stage2 and table_stage3 can be executed in parallel, but using all the tuples returned by table_stage1, while the final insert will be executed by using all the tuples returned by the previous two statements (and so by using all the tuples produced by the previous three statements).

Note that: “B uses all the tuples returned by A” is not necessarily equivalent to: “B is executed after A”: the optimizer can in fact transform B so that it is not necessary to execute A. It is just a semantics definition, and is not related to an actual implementation.