public inbox for [email protected]  

help / color / mirror / Atom feed

From: Renan Alves Fonseca <[email protected]>
To: Chris Joysn <[email protected]>
Cc: [email protected]
Subject: Re: Very slow query performance when using CTE
Date: Tue, 01 Apr 2025 22:03:29 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKRYhW36jEbPPSPoiPkNcPWdrEXOtdugw9Hyg1poHju7LECHJg@mail.gmail.com>
References: <CAKRYhW36jEbPPSPoiPkNcPWdrEXOtdugw9Hyg1poHju7LECHJg@mail.gmail.com>

Chris Joysn <[email protected]> writes:

> Hello,
> I have an issue when using CTEs. A query, which consists of multiple CTEs, runs usually rather fast (~5s on my
> environment). But it turned out that using one CTE can lead to execution times of up to one minute.
> That CTE is used two times within the query. In the CTE there are 2600 rows, compared to results of the other CTEs its a
> fraction of the data.
> When replacing this CTE and use the original table instead in the jions, the query performs nicely.
> However, it is not always like this. Running the same query on a almost same set of data, quantity wise, may give indeed
> good performance when using that CTE.
> This is the slow performing query using CTE: 
> https://explain.dalibo.com/plan/45ce86d9cfge14c7
> And this is the fast performing query without that CTE: 
> https://explain.dalibo.com/plan/4abgc4773gg349b4
>
> The query runs on the very same environment and data.
> What can be the issue here and how can I address it?


Hi,

the planner gets really bad estimates on the number of rows that the
first two CTE return. It is the same situation in both queries. It is
just an accident that one of them works fine.

We need to understand why these estimates are wrong. The protocol to fix
this kind of issues is to apply ANALYZE so that the statistics get
update. You can try that, but my guess is that we are dealing with a
generic plan here. So, try to replace the value of $simRunId by the
actual value and see if the plan changes.

Best regards,
Renan

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Very slow query performance when using CTE
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox