[postgis-users] Queries slow after PostGIS upgrade

[Regina Obe]

> > 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 [...]
> 
> I've been stroke by this issue multiple times. Making the default
different from
> previous versions was probably a mistake here...
> 
> --strk;

FWIW, if you need your code to run both in < 12 and >= 12

Using something like OFFSET 0 is a better option.
>From what Tom Lane told me a long time ago, I think that (at least I think
still does) always forces a materialization.  

So something like 

WITH a AS (SELECT ... FROM sometable OFFSET 0)

I recall doing that to fix some of the PostGIS regressions resulting from
the CTE change in behavior