[postgis-users] Queries slow after PostGIS upgrade

[Chris Mair]

> 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.