> We implemented Chris’s suggestion > > with product as (... > with aoi as (... > you give > with product as MATERIALIZED (... > with aoi as MATERIALIZED (... > and see the explain analyze? > > The query is now running in 4-5 seconds so using MATERIALIZED appears to have > resolved our issue. Thanks for your suggestion Chris. >
Very good! So the problem here was an optimization introduced in Postgres 12, in your case it did more harm than good: https://www.postgresql.org/docs/12/release-12.html#id-1.11.6.17.5.3.4 Quote: Specifically, CTEs are automatically inlined [...] Inlining can be prevented by specifying MATERIALIZED [...] Previously, CTEs were never inlined [...] Bye, Chris. _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
