explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ozf : Optimization for: plan #KRaE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.456 1,121,718.424 ↑ 1,534.6 654 1

Hash Left Join (cost=1,032,042.99..1,076,403.88 rows=1,003,640 width=253) (actual time=1,119,928.644..1,121,718.424 rows=654 loops=1)

  • Hash Cond: (d.booking_id = fg.booking_id)
2.          

CTE booking_ids

3. 157.497 139,061.774 ↑ 165.7 998 1

Hash Join (cost=37.00..125,749.66 rows=165,342 width=12) (actual time=67.729..139,061.774 rows=998 loops=1)

  • Hash Cond: ((i.id)::double precision = (floor(((random() * '130201'::double precision) + '200000'::double precision))))
4. 138,903.550 138,903.550 ↑ 1.0 329,987 1

Seq Scan on booking i (cost=0.00..122,777.84 rows=330,684 width=12) (actual time=0.021..138,903.550 rows=329,987 loops=1)

5. 0.113 0.727 ↓ 5.0 998 1

Hash (cost=34.50..34.50 rows=200 width=8) (actual time=0.727..0.727 rows=998 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
6. 0.372 0.614 ↓ 5.0 998 1

HashAggregate (cost=32.50..34.50 rows=200 width=8) (actual time=0.488..0.614 rows=998 loops=1)

  • Group Key: floor(((random() * '130201'::double precision) + '200000'::double precision))
7. 0.242 0.242 ↑ 1.0 1,000 1

Function Scan on generate_series (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.111..0.242 rows=1,000 loops=1)

8.          

CTE booking_groups

9. 0.174 74,125.914 ↑ 6.6 267 1

GroupAggregate (cost=67,202.76..67,261.82 rows=1,761 width=19) (actual time=74,125.757..74,125.914 rows=267 loops=1)

  • Group Key: f.booking_id, f."group
10. 0.136 74,125.740 ↑ 8.4 268 1

Sort (cost=67,202.76..67,208.41 rows=2,259 width=19) (actual time=74,125.721..74,125.740 rows=268 loops=1)

  • Sort Key: f.booking_id, f."group
  • Sort Method: quicksort Memory: 45kB
11. 39.444 74,125.604 ↑ 8.4 268 1

Hash Join (cost=62,714.10..67,076.92 rows=2,259 width=19) (actual time=74,119.243..74,125.604 rows=268 loops=1)

  • Hash Cond: (i_1.booking_id = f.booking_id)
12. 0.296 0.296 ↑ 165.7 998 1

CTE Scan on booking_ids i_1 (cost=0.00..3,306.84 rows=165,342 width=4) (actual time=0.001..0.296 rows=998 loops=1)

13. 81.302 74,085.864 ↓ 44.2 78,262 1

Hash (cost=62,691.99..62,691.99 rows=1,769 width=19) (actual time=74,085.864..74,085.864 rows=78,262 loops=1)

  • Buckets: 131,072 (originally 2048) Batches: 2 (originally 1) Memory Usage: 3,329kB
14. 14.039 74,004.562 ↓ 44.2 78,262 1

Gather (cost=1,000.00..62,691.99 rows=1,769 width=19) (actual time=42.132..74,004.562 rows=78,262 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
15. 73,990.523 73,990.523 ↓ 106.2 78,262 1

Parallel Seq Scan on inspections_inspectionfixedgroup f (cost=0.00..61,515.09 rows=737 width=19) (actual time=41.735..73,990.523 rows=78,262 loops=1)

  • Filter: (upper("group") = 'MEASUREMENT'::text)
  • Rows Removed by Filter: 280,434
16.          

CTE msm_defects

17. 16.425 1,047,590.405 ↑ 1,534.6 654 1

Merge Join (cost=822,723.06..838,987.38 rows=1,003,640 width=131) (actual time=1,045,802.435..1,047,590.405 rows=654 loops=1)

  • Merge Cond: (d_1.booking_id = i_2.booking_id)
18. 5,144.448 908,505.311 ↑ 1.1 150,238 1

Sort (cost=802,257.12..802,655.30 rows=159,273 width=123) (actual time=904,688.699..908,505.311 rows=150,238 loops=1)

  • Sort Key: d_1.booking_id
  • Sort Method: external merge Disk: 17,792kB
19. 0.000 903,360.863 ↑ 1.1 150,239 1

Gather (cost=1,000.00..778,149.02 rows=159,273 width=123) (actual time=0.408..903,360.863 rows=150,239 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 903,415.167 903,415.167 ↑ 1.3 50,080 3 / 3

Parallel Seq Scan on inspections_inspectionmeasurementdimension d_1 (cost=0.00..761,221.72 rows=66,364 width=123) (actual time=15.215..903,415.167 rows=50,080 loops=3)

  • Filter: (occurrences_count > 0)
  • Rows Removed by Filter: 3,984,169
21. 0.189 139,068.669 ↑ 108.8 1,519 1

Materialize (cost=20,465.94..21,292.65 rows=165,342 width=12) (actual time=139,068.344..139,068.669 rows=1,519 loops=1)

22. 2.073 139,068.480 ↑ 165.7 998 1

Sort (cost=20,465.94..20,879.29 rows=165,342 width=12) (actual time=139,068.335..139,068.480 rows=998 loops=1)

  • Sort Key: i_2.booking_id
  • Sort Method: quicksort Memory: 71kB
23. 139,066.407 139,066.407 ↑ 165.7 998 1

CTE Scan on booking_ids i_2 (cost=0.00..3,306.84 rows=165,342 width=12) (actual time=67.737..139,066.407 rows=998 loops=1)

24. 1,047,590.815 1,047,590.815 ↑ 1,534.6 654 1

CTE Scan on msm_defects d (cost=0.00..20,072.80 rows=1,003,640 width=116) (actual time=1,045,802.439..1,047,590.815 rows=654 loops=1)

25. 0.046 74,126.153 ↓ 29.7 267 1

Hash (cost=44.03..44.03 rows=9 width=8) (actual time=74,126.153..74,126.153 rows=267 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
26. 74,126.107 74,126.107 ↓ 29.7 267 1

CTE Scan on booking_groups fg (cost=0.00..44.03 rows=9 width=8) (actual time=74,125.771..74,126.107 rows=267 loops=1)

  • Filter: ('MEASUREMENT'::text = upper("group"))
Planning time : 152.461 ms
Execution time : 1,121,791.488 ms