On Fri, 2021-03-05 at 17:55 +0000, val.janeiko@xxxxxxxxx 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
