PostgreSQL(PostGIS): Calling ST_Intersects for result of WITH clause

I have a set of linked tables. Last of them has geometry column. I need to get data from this set of tables by some criteria (including geometry intersection). The idea is to apply intersection analysis on the last step to speed up query.

So I do filtration by all criteria except intersection:

SELECT table1.someColumn, table2.someColumn, table3.someColumn, table4.geom
FROM table1
INNER JOIN table2 ON <condition>
INNER JOIN table3 ON <condition>
INNER JOIN table4 ON <condition>
WHERE <some condition> AND <another condition>

This query runs near 5 seconds and return 10 records only. Then I want to apply filtration to these 10 records only by intersection. So full query looks like

WITH res AS (
    SELECT table1.someColumn, table2.someColumn, table3.someColumn, table4.geom
    FROM table1
    INNER JOIN table2 ON <condition>
    INNER JOIN table3 ON <condition>
    INNER JOIN table4 ON <condition>
    WHERE <some condition> AND <another condition>
)
SELECT * FROM res WHERE ST_Intersects(res.geom, <my bbox geometry>)

But, for some reason, this query takes more than 3 minutes for execution.

I've checked execution plan and found that postgres scans for intersection on the first step for all records from table4 (more than 1M) and then apply all other conditions.

I've also tried to do in other way:

SELECT table1.someColumn, table2.someColumn, table3.someColumn, ST_Intersects(res.geom, <my bbox geometry>) AS int
FROM table1
INNER JOIN table2 ON <condition>
INNER JOIN table3 ON <condition>
INNER JOIN table4 ON <condition>
WHERE <some condition> AND <another condition>

It takes same 5 seconds and result includes true\false column for intersection results. But then I do:

WITH res AS (
    SELECT table1.someColumn, table2.someColumn, table3.someColumn, ST_Intersects(res.geom, <my bbox geometry>) AS int
    FROM table1
    INNER JOIN table2 ON <condition>
    INNER JOIN table3 ON <condition>
    INNER JOIN table4 ON <condition>
    WHERE <some condition> AND <another condition>
)
SELECT * FROM res WHERE res.int IS true

And again it takes more than 3 minutes for execution.

Does anybody face same kind of issue?

Solution

You can force Postgres to execute the CTE first instead of inlining it within the final query by materializing it.

WITH res AS MATERIALIZED (...)