pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow query performance inside a transaction on a clean database
2+ messages / 2 participants
[nested] [flat]
* Slow query performance inside a transaction on a clean database
@ 2021-03-05 17:55 val.janeiko@gmail.com
2021-03-08 09:17 ` Re: Slow query performance inside a transaction on a clean database Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 2+ messages in thread
From: val.janeiko@gmail.com @ 2021-03-05 17:55 UTC (permalink / raw)
To: pgsql-performance
Hi everyone,
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.
The slow query is part of the transaction (with Repeatable Read isolation
level) that executes *create if not exists* type flow for 211 resources.
Each resource insertion, inserts multiple rows into each table. Each
resource has a unique `resource_surrogate_id`, which is part of every row
inserted for that resource. Inside a transaction search is performed using
values for a resource, before that resource is inserted, hence it always
returns 0 rows (only date values are changing and never overlap). Resources
are inserted in the order of increasing `resource_surrogate_id` (only
`resource_type_id == 52` rows are part of the transaction).
Some more info about the CTEs:
- cte0: always matches N rows (from 5*N rows inserted for each resource)
- cte1: always matches N rows (from 5*N rows inserted for each resource)
- cte2: always matches N rows (from 17*N rows inserted for each resource)
- cte3 when combined with cte4: always matches 0 rows (from 2*N rows
inserted for each resource)
- (if insertions are ordered by start_date_time, cte3 matches 0 rows, and
query is fast - execution plan not included)
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
I have also created a gist:
https://gist.github.com/anyname2/e908d13d515e8970e599eb650cab15fe
- init.sql - is a script to create tables and indexes
- parametrized-query.sql - is the query being executed
- pgdump.sql - database dump
PostgreSQL Version: PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc
(Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
Setup: PostgreSQL is running inside a docker container (with default
parameters), issue is reproducible both in the Kubernetes cluster and
locally.
Can anyone help diagnose this?
There are a few question I have:
- Repeatable Read transaction is running on an empty database, hence it
should not match anything. Why are resources inserted in the current
transaction considered in the query? (if I understood the execution plan
correctly)
- What causes the slow case? How can I rewrite the query to avoid the slow
case?
- Is it my query not optimised? Or should the execution planner handle it
better somehow?
Thank you,
Valentinas
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Slow query performance inside a transaction on a clean database
2021-03-05 17:55 Slow query performance inside a transaction on a clean database val.janeiko@gmail.com
@ 2021-03-08 09:17 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2021-03-08 09:17 UTC (permalink / raw)
To: val.janeiko@gmail.com; pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-03-08 09:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-05 17:55 Slow query performance inside a transaction on a clean database val.janeiko@gmail.com
2021-03-08 09:17 ` Laurenz Albe <laurenz.albe@cybertec.at>
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