Re: Slow query performance inside a transaction on a clean database

On Fri, 2021-03-05 at 17:55 +0000, val(dot)janeiko(at)gmail(dot)com wrote:
> I have a SELECT query that uses a long chain of CTEs (6) and is executed
> repeatedly as part of the transaction (with different parameters). It is
> executed quickly most of the time, but sometimes becomes very slow. I
> managed to consistently reproduce the issue by executing a transaction
> containing this query on an empty database. The query is fast for the first
> 150-170 inserted resources, but ~50% of the executions afterwards take 5.6s
> instead of 1.4ms. Additionally it only becomes slow if resources are
> inserted in a random order, if I insert resources sorted by
> `start_date_time` column the query is always fast.
>
> Here are the results of EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT
> JSON):
> - Slow: https://explain.depesz.com/s/e4Fo
> - Fast: https://explain.depesz.com/s/7HFJ

If your transaction modifies the data significantly (which it does if the
table is empty before you start), you should throw in an ANALYZE on the
affected tables occasionally.

Normally, autovacuum takes care of that, but it cannot see your data
until the transaction is committed.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com