From: Laurenz Albe <laurenz.albe@cybertec.at>
To: val.janeiko@gmail.com
To: pgsql-performance@postgresql.org
Subject: Re: Slow query performance inside a transaction on a clean database
Date: Mon, 08 Mar 2021 10:17:59 +0100
Message-ID: <ecab6bec627ea59c3dcc84f236e04d745caf0a79.camel@cybertec.at> (raw)
In-Reply-To: <002101d711e8$bfd8a490$3f89edb0$@gmail.com>
References: <002101d711e8$bfd8a490$3f89edb0$@gmail.com>
On Fri, 2021-03-05 at 17:55 +0000, val.janeiko@gmail.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
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: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, val.janeiko@gmail.com
Subject: Re: Slow query performance inside a transaction on a clean database
In-Reply-To: <ecab6bec627ea59c3dcc84f236e04d745caf0a79.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox