Re: Poor Performance running Django unit tests after upgrading from 10.6

On 10/15/20 1:21 AM, Roger Hunwicks wrote:
>
> I think we have narrowed down the problem to a single, very complex,
> materialized view using CTEs; the unit tests create the test data and
> then refresh the materialized view before executing the actual test
> code.
>

Have you checked to see if the CTE query is affected by the change to
how CTEs are run in release 12?

The release notes say:

Allow common table expressions (CTEs) to be inlined into the outer
query (Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)

Specifically, CTEs are automatically inlined if they have no
side-effects, are not recursive, and are referenced only once in the
query. Inlining can be prevented by specifying MATERIALIZED, or
forced for multiply-referenced CTEs by specifying NOT MATERIALIZED.
Previously, CTEs were never inlined and were always evaluated before
the rest of the query.

So if you haven't already, start by putting MATERIALIZED before each CTE
clause:

with foo as MATERIALIZED (select ...),

bar as MATERIALIZED  (select ...),

...

and see if that changes anything.

cheers

andrew

--
Andrew Dunstan
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company