explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KRaE

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5.384 725,970.552 ↑ 2,937.5 1,107 1

Hash Left Join (cost=1,541,150.98..1,868,196.11 rows=3,251,794 width=265) (actual time=695,741.777..725,970.552 rows=1,107 loops=1)

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

CTE booking_ids

3. 66.500 657.240 ↑ 83.3 1,985 1

Hash Join (cost=37.00..125,749.66 rows=165,342 width=12) (actual time=2.963..657.240 rows=1,985 loops=1)

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

Seq Scan on booking i (cost=0.00..122,777.84 rows=330,684 width=12) (actual time=0.034..589.220 rows=329,959 loops=1)

5. 0.242 1.520 ↓ 9.9 1,985 1

Hash (cost=34.50..34.50 rows=200 width=8) (actual time=1.520..1.520 rows=1,985 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 94kB
6. 0.855 1.278 ↓ 9.9 1,985 1

HashAggregate (cost=32.50..34.50 rows=200 width=8) (actual time=1.032..1.278 rows=1,985 loops=1)

  • Group Key: floor(((random() * '130201'::double precision) + '200000'::double precision))
7. 0.423 0.423 ↓ 2.0 2,000 1

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

8.          

CTE booking_groups

9. 1.359 53,593.629 ↑ 63.8 2,030 1

GroupAggregate (cost=133,248.35..141,160.72 rows=129,500 width=19) (actual time=53,592.273..53,593.629 rows=2,030 loops=1)

  • Group Key: f.booking_id, f."group
10. 2.184 53,592.270 ↑ 222.5 2,030 1

Sort (cost=133,248.35..134,377.58 rows=451,689 width=19) (actual time=53,592.071..53,592.270 rows=2,030 loops=1)

  • Sort Key: f.booking_id, f."group
  • Sort Method: quicksort Memory: 207kB
11. 2,031.265 53,590.086 ↑ 222.5 2,030 1

Hash Join (cost=69,336.91..81,559.03 rows=451,689 width=19) (actual time=51,559.221..53,590.086 rows=2,030 loops=1)

  • Hash Cond: (i_1.booking_id = f.booking_id)
12. 0.685 0.685 ↑ 83.3 1,985 1

CTE Scan on booking_ids i_1 (cost=0.00..3,306.84 rows=165,342 width=4) (actual time=0.002..0.685 rows=1,985 loops=1)

13. 173.038 51,558.136 ↓ 1.0 358,694 1

Hash (cost=62,841.74..62,841.74 rows=353,774 width=19) (actual time=51,558.136..51,558.136 rows=358,694 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,793kB
14. 51,385.098 51,385.098 ↓ 1.0 358,694 1

Seq Scan on inspections_inspectionfixedgroup f (cost=0.00..62,841.74 rows=353,774 width=19) (actual time=17.084..51,385.098 rows=358,694 loops=1)

15.          

CTE msm_defects

16. 17.119 430,456.432 ↑ 906.6 1,107 1

Merge Join (cost=822,723.06..838,987.38 rows=1,003,640 width=131) (actual time=428,909.598..430,456.432 rows=1,107 loops=1)

  • Merge Cond: (d_1.booking_id = i_2.booking_id)
17. 3,101.921 429,778.488 ↑ 1.1 150,238 1

Sort (cost=802,257.12..802,655.30 rows=159,273 width=123) (actual time=427,692.788..429,778.488 rows=150,238 loops=1)

  • Sort Key: d_1.booking_id
  • Sort Method: external merge Disk: 17,792kB
18. 0.000 426,676.567 ↑ 1.1 150,239 1

Gather (cost=1,000.00..778,149.02 rows=159,273 width=123) (actual time=2.880..426,676.567 rows=150,239 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 426,717.279 426,717.279 ↑ 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=1.693..426,717.279 rows=50,080 loops=3)

  • Filter: (occurrences_count > 0)
  • Rows Removed by Filter: 3,984,169
20. 0.390 660.825 ↑ 57.7 2,864 1

Materialize (cost=20,465.94..21,292.65 rows=165,342 width=12) (actual time=660.104..660.825 rows=2,864 loops=1)

21. 1.251 660.435 ↑ 83.3 1,985 1

Sort (cost=20,465.94..20,879.29 rows=165,342 width=12) (actual time=660.099..660.435 rows=1,985 loops=1)

  • Sort Key: i_2.booking_id
  • Sort Method: quicksort Memory: 142kB
22. 659.184 659.184 ↑ 83.3 1,985 1

CTE Scan on booking_ids i_2 (cost=0.00..3,306.84 rows=165,342 width=12) (actual time=2.969..659.184 rows=1,985 loops=1)

23. 335.801 672,369.947 ↑ 906.6 1,107 1

Hash Left Join (cost=432,007.63..553,055.63 rows=1,003,640 width=128) (actual time=642,146.450..672,369.947 rows=1,107 loops=1)

  • Hash Cond: (bi.product_category_id = pc.id)
24. 28,542.085 670,437.470 ↑ 906.6 1,107 1

Hash Left Join (cost=426,333.76..507,023.11 rows=1,003,640 width=124) (actual time=640,548.783..670,437.470 rows=1,107 loops=1)

  • Hash Cond: (d.item_id = bi.id)
25. 430,457.742 430,457.742 ↑ 906.6 1,107 1

CTE Scan on msm_defects d (cost=0.00..20,072.80 rows=1,003,640 width=116) (actual time=428,909.603..430,457.742 rows=1,107 loops=1)

26. 1,732.376 211,437.643 ↓ 1.0 4,662,031 1

Hash (cost=345,536.78..345,536.78 rows=4,648,078 width=12) (actual time=211,437.643..211,437.643 rows=4,662,031 loops=1)

  • Buckets: 131,072 Batches: 128 Memory Usage: 2,552kB
27. 209,705.267 209,705.267 ↓ 1.0 4,662,031 1

Seq Scan on inspections_bookingitem bi (cost=0.00..345,536.78 rows=4,648,078 width=12) (actual time=18.491..209,705.267 rows=4,662,031 loops=1)

28. 44.257 1,596.676 ↑ 1.0 122,261 1

Hash (cost=3,667.61..3,667.61 rows=122,261 width=8) (actual time=1,596.676..1,596.676 rows=122,261 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,421kB
29. 1,552.419 1,552.419 ↑ 1.0 122,261 1

Seq Scan on datasource_productcategory pc (cost=0.00..3,667.61 rows=122,261 width=8) (actual time=16.835..1,552.419 rows=122,261 loops=1)

30. 0.101 53,595.221 ↑ 1.3 501 1

Hash (cost=3,237.50..3,237.50 rows=648 width=8) (actual time=53,595.220..53,595.221 rows=501 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
31. 53,595.120 53,595.120 ↑ 1.3 501 1

CTE Scan on booking_groups fg (cost=0.00..3,237.50 rows=648 width=8) (actual time=53,592.292..53,595.120 rows=501 loops=1)

  • Filter: ('MEASUREMENT'::text = upper("group"))
  • Rows Removed by Filter: 1,529
Planning time : 37.359 ms
Execution time : 725,972.884 ms