explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mMCb

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 1,900.061 ↑ 1.0 1 1

Aggregate (cost=239,521.08..239,521.10 rows=1 width=160) (actual time=1,900.061..1,900.061 rows=1 loops=1)

2. 0.023 1,900.040 ↑ 21.0 20 1

Nested Loop Left Join (cost=115,656.18..239,512.66 rows=420 width=128) (actual time=1,770.053..1,900.040 rows=20 loops=1)

3. 0.217 1,899.237 ↑ 10.5 20 1

Merge Left Join (cost=115,655.75..237,689.39 rows=210 width=100) (actual time=1,769.988..1,899.237 rows=20 loops=1)

  • Merge Cond: (ownrq.id = rrp_1.roadworkrequestid)
4. 629.518 1,804.809 ↑ 10.5 20 1

Gather Merge (cost=105,980.18..227,759.14 rows=210 width=68) (actual time=1,681.193..1,804.809 rows=20 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 30.202 1,175.291 ↑ 12.6 7 3 / 3

Merge Left Join (cost=104,980.16..226,734.88 rows=88 width=68) (actual time=1,134.103..1,175.291 rows=7 loops=3)

  • Merge Cond: (ownrq.id = rra.roadworkrequestid)
6. 6.299 410.088 ↑ 12.6 7 3 / 3

Merge Left Join (cost=104,979.73..112,848.18 rows=88 width=36) (actual time=403.152..410.088 rows=7 loops=3)

  • Merge Cond: (ownrq.id = rrp.roadworkrequestid)
7. 0.028 175.978 ↑ 12.6 7 3 / 3

Sort (cost=60,901.44..60,901.66 rows=88 width=4) (actual time=175.975..175.978 rows=7 loops=3)

  • Sort Key: ownrq.id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
8. 175.950 175.950 ↑ 12.6 7 3 / 3

Parallel Seq Scan on roadworkrequest ownrq (cost=0.00..60,898.59 rows=88 width=4) (actual time=75.277..175.950 rows=7 loops=3)

  • Filter: ((psmexportid = 110,250) OR (ownerexportid = 110,250))
  • Rows Removed by Filter: 458,866
9. 71.779 227.811 ↓ 1.2 264,523 2 / 3

GroupAggregate (cost=44,078.29..49,116.32 rows=226,387 width=36) (actual time=212.286..341.717 rows=264,523 loops=2)

  • Group Key: rrp.roadworkrequestid
10. 70.731 156.033 ↑ 1.1 276,323 2 / 3

Sort (cost=44,078.29..44,814.35 rows=294,426 width=9) (actual time=212.270..234.049 rows=276,323 loops=2)

  • Sort Key: rrp.roadworkrequestid
  • Sort Method: external merge Disk: 5,640kB
  • Worker 1: Sort Method: external merge Disk: 5,640kB
11. 28.043 85.302 ↑ 1.0 291,637 2 / 3

Hash Join (cost=674.22..12,300.30 rows=294,426 width=9) (actual time=5.210..127.953 rows=291,637 loops=2)

  • Hash Cond: (rrp.penaltytypeid = p.id)
12. 53.955 53.955 ↓ 1.0 298,557 2 / 3

Seq Scan on roadworkrequestpenalty rrp (cost=0.00..10,848.15 rows=296,297 width=13) (actual time=0.130..80.932 rows=298,557 loops=2)

  • Filter: ispsm
  • Rows Removed by Filter: 207,558
13. 1.335 3.304 ↑ 1.0 20,618 2 / 3

Hash (cost=416.49..416.49 rows=20,618 width=4) (actual time=4.956..4.956 rows=20,618 loops=2)

  • Buckets: 32,768 Batches: 1 Memory Usage: 981kB
14. 1.969 1.969 ↑ 1.0 20,618 2 / 3

Seq Scan on penalty p (cost=0.00..416.49 rows=20,618 width=4) (actual time=0.087..2.953 rows=20,618 loops=2)

  • Filter: (NOT islegalfee)
  • Rows Removed by Filter: 131
15. 299.073 735.001 ↓ 1.3 1,148,953 2 / 3

GroupAggregate (cost=0.43..102,673.74 rows=896,974 width=36) (actual time=0.205..1,102.501 rows=1,148,953 loops=2)

  • Group Key: rra.roadworkrequestid
16. 435.928 435.928 ↑ 1.0 1,148,954 2 / 3

Index Scan using idx_roadworkrequestadditional_roadworkrequestid_ispsm on roadworkrequestadditional rra (cost=0.43..85,513.96 rows=1,189,520 width=7) (actual time=0.195..653.892 rows=1,148,954 loops=2)

  • Index Cond: (ispsm = true)
  • Filter: ispsm
17. 3.849 94.211 ↓ 3.5 6,607 1

Finalize GroupAggregate (cost=9,675.57..9,906.32 rows=1,871 width=36) (actual time=86.646..94.211 rows=6,607 loops=1)

  • Group Key: rrp_1.roadworkrequestid
18. 53.213 90.362 ↓ 4.2 6,608 1

Gather Merge (cost=9,675.57..9,871.24 rows=1,560 width=36) (actual time=86.635..90.362 rows=6,608 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 1.149 37.149 ↓ 2.8 2,203 3 / 3

Partial GroupAggregate (cost=8,675.55..8,691.15 rows=780 width=36) (actual time=35.885..37.149 rows=2,203 loops=3)

  • Group Key: rrp_1.roadworkrequestid
20. 0.768 36.000 ↓ 2.8 2,203 3 / 3

Sort (cost=8,675.55..8,677.50 rows=780 width=9) (actual time=35.877..36.000 rows=2,203 loops=3)

  • Sort Key: rrp_1.roadworkrequestid
  • Sort Method: quicksort Memory: 474kB
  • Worker 0: Sort Method: quicksort Memory: 68kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
21. 4.953 35.232 ↓ 3.0 2,307 3 / 3

Hash Join (cost=418.13..8,638.08 rows=780 width=9) (actual time=3.230..35.232 rows=2,307 loops=3)

  • Hash Cond: (rrp_1.penaltytypeid = p_1.id)
22. 27.561 27.561 ↑ 1.2 99,519 3 / 3

Parallel Seq Scan on roadworkrequestpenalty rrp_1 (cost=0.00..7,895.81 rows=123,457 width=13) (actual time=0.141..27.561 rows=99,519 loops=3)

  • Filter: ispsm
  • Rows Removed by Filter: 69,186
23. 0.022 2.718 ↑ 1.0 131 3 / 3

Hash (cost=416.49..416.49 rows=131 width=4) (actual time=2.718..2.718 rows=131 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
24. 2.696 2.696 ↑ 1.0 131 3 / 3

Seq Scan on penalty p_1 (cost=0.00..416.49 rows=131 width=4) (actual time=0.108..2.696 rows=131 loops=3)

  • Filter: islegalfee
  • Rows Removed by Filter: 20,618
25. 0.040 0.780 ↑ 2.0 1 20

GroupAggregate (cost=0.43..8.64 rows=2 width=36) (actual time=0.039..0.039 rows=1 loops=20)

  • Group Key: rrm.roadworkrequestid
26. 0.740 0.740 ↑ 2.0 1 20

Index Scan using ix_roadworkrequestmaterial_roadworkrequestid on roadworkrequestmaterial rrm (cost=0.43..8.61 rows=2 width=10) (actual time=0.035..0.037 rows=1 loops=20)

  • Index Cond: (roadworkrequestid = ownrq.id)
  • Filter: ispsm
  • Rows Removed by Filter: 1