explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pAXH

Settings
# exclusive inclusive rows x rows loops node
1. 21.224 995.144 ↓ 2,385.0 2,385 1

Sort (cost=451.26..451.27 rows=1 width=64) (actual time=985.031..995.144 rows=2,385 loops=1)

  • Sort Key: (count(1)) DESC
  • Sort Method: quicksort Memory: 283kB
2.          

CTE state_times

3. 36.425 139.619 ↓ 136.6 3,552 1

WindowAgg (cost=448.86..449.58 rows=26 width=80) (actual time=87.004..139.619 rows=3,552 loops=1)

4. 32.979 103.194 ↓ 136.6 3,552 1

Sort (cost=448.86..448.93 rows=26 width=48) (actual time=86.978..103.194 rows=3,552 loops=1)

  • Sort Key: soe.sales_order_id, soe.created_at
  • Sort Method: quicksort Memory: 522kB
5. 31.279 70.215 ↓ 136.6 3,552 1

Hash Join (cost=126.26..448.25 rows=26 width=48) (actual time=20.854..70.215 rows=3,552 loops=1)

  • Hash Cond: (soe.sales_order_id = so.id)
6. 18.122 18.122 ↓ 111.5 3,568 1

Seq Scan on sales_order_event soe (cost=0.00..321.91 rows=32 width=32) (actual time=0.024..18.122 rows=3,568 loops=1)

  • Filter: ((created_at <@ '["2020-09-01 00:00:00+03","2020-10-01 00:00:00+03")'::tstzrange) AND ((old_delivery_status)::text = ANY ('{PENDING,IN_COLLECTION,COLLECTED}'::text[])))
  • Rows Removed by Filter: 7,934
7. 10.140 20.814 ↑ 1.0 2,091 1

Hash (cost=100.12..100.12 rows=2,091 width=32) (actual time=20.810..20.814 rows=2,091 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 163kB
8. 10.674 10.674 ↑ 1.0 2,091 1

Seq Scan on sales_order so (cost=0.00..100.12 rows=2,091 width=32) (actual time=0.014..10.674 rows=2,091 loops=1)

  • Filter: (site_id <> ALL ('{72313b34-ca87-4467-82c1-5d664091539f,52e12f2f-4bb9-41ed-a054-785a6d9fe807,162ca18b-9018-4526-addf-ef6fb13c00c3,13fbb901-0721-4b20-8c12-415e1913a103,28c33e10-4785-495e-9d92-dd8c0b138eff,df7e16b1-55fd-4948-9a09-827274f95072,5642aa7c-ffb6-4a4b-b777-2bfefd3cb952,0e99dd9d-a530-4939-8403-a93eff7e4023}'::uuid[]))
  • Rows Removed by Filter: 465
9. 35.001 973.920 ↓ 2,385.0 2,385 1

GroupAggregate (cost=1.65..1.68 rows=1 width=64) (actual time=917.208..973.920 rows=2,385 loops=1)

  • Group Key: s1.state_started, s1.site_id, s1.state_name
10. 47.953 938.919 ↓ 4,992.0 4,992 1

Sort (cost=1.65..1.66 rows=1 width=56) (actual time=917.185..938.919 rows=4,992 loops=1)

  • Sort Key: s1.state_started, s1.site_id, s1.state_name
  • Sort Method: quicksort Memory: 583kB
11. 682.125 890.966 ↓ 4,992.0 4,992 1

Hash Join (cost=0.91..1.64 rows=1 width=56) (actual time=190.782..890.966 rows=4,992 loops=1)

  • Hash Cond: ((s1.site_id = s2.site_id) AND (s1.state_name = s2.state_name))
  • Join Filter: ((s2.state_started <= s1.state_started) AND (s1.state_started < s2.state_ended))
  • Rows Removed by Join Filter: 7,009,032
12. 105.287 105.287 ↓ 136.6 3,552 1

CTE Scan on state_times s1 (cost=0.00..0.52 rows=26 width=56) (actual time=87.014..105.287 rows=3,552 loops=1)

13. 17.273 103.554 ↓ 136.6 3,552 1

Hash (cost=0.52..0.52 rows=26 width=64) (actual time=103.550..103.554 rows=3,552 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 282kB
14. 86.281 86.281 ↓ 136.6 3,552 1

CTE Scan on state_times s2 (cost=0.00..0.52 rows=26 width=64) (actual time=0.004..86.281 rows=3,552 loops=1)

Planning time : 0.251 ms
Execution time : 1,005.243 ms