explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5HXsb

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,201.711 ↓ 5.0 5 1

Limit (cost=13,859.51..13,859.51 rows=1 width=288) (actual time=1,201.708..1,201.711 rows=5 loops=1)

2.          

CTE b

3. 921.283 957.084 ↓ 5.4 105,990 1

HashAggregate (cost=4,335.97..9,548.26 rows=19,669 width=42) (actual time=135.943..957.084 rows=105,990 loops=1)

  • Group Key: r_1.comp_part, r_1.sernr
4. 35.801 35.801 ↑ 1.0 129,827 1

Seq Scan on so_reportings r_1 (cost=0.00..3,362.27 rows=129,827 width=15) (actual time=0.006..35.801 rows=129,827 loops=1)

5. 0.011 1,201.708 ↓ 5.0 5 1

Sort (cost=4,311.25..4,311.25 rows=1 width=288) (actual time=1,201.707..1,201.708 rows=5 loops=1)

  • Sort Key: a.sernr, a.id
  • Sort Method: quicksort Memory: 26kB
6. 0.007 1,201.697 ↓ 5.0 5 1

Subquery Scan on a (cost=4,311.22..4,311.24 rows=1 width=288) (actual time=1,201.692..1,201.697 rows=5 loops=1)

7. 0.023 1,201.690 ↓ 5.0 5 1

Sort (cost=4,311.22..4,311.23 rows=1 width=292) (actual time=1,201.689..1,201.690 rows=5 loops=1)

  • Sort Key: so_reservings.sernr, sr.rowno
  • Sort Method: quicksort Memory: 26kB
8. 0.031 1,201.667 ↓ 5.0 5 1

Nested Loop Left Join (cost=3,754.42..4,311.21 rows=1 width=292) (actual time=490.095..1,201.667 rows=5 loops=1)

  • Join Filter: ((NOT (sr.alt_code IS DISTINCT FROM so_reservings.alt_code)) AND (sr.component_part = so_reservings.comp_part))
  • Rows Removed by Join Filter: 20
9. 0.017 1,201.461 ↓ 5.0 5 1

Nested Loop Left Join (cost=3,754.13..4,310.44 rows=1 width=292) (actual time=490.067..1,201.461 rows=5 loops=1)

10. 0.015 1,201.404 ↓ 5.0 5 1

Nested Loop Left Join (cost=3,753.85..4,302.80 rows=1 width=290) (actual time=490.051..1,201.404 rows=5 loops=1)

11. 0.023 1,201.369 ↓ 5.0 5 1

Nested Loop Left Join (cost=3,753.72..4,302.64 rows=1 width=258) (actual time=490.040..1,201.369 rows=5 loops=1)

12. 50.113 1,201.306 ↓ 5.0 5 1

Hash Right Join (cost=3,753.30..4,294.21 rows=1 width=253) (actual time=490.019..1,201.306 rows=5 loops=1)

  • Hash Cond: ((ro.sernr = so_reservings.sernr) AND (ro.comp_part = so_reservings.comp_part))
13. 1,043.451 1,043.451 ↓ 5.4 105,990 1

CTE Scan on b ro (cost=0.00..393.38 rows=19,669 width=68) (actual time=135.947..1,043.451 rows=105,990 loops=1)

14. 0.015 107.742 ↓ 5.0 5 1

Hash (cost=3,753.28..3,753.28 rows=1 width=221) (actual time=107.741..107.742 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.018 107.727 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.29..3,753.28 rows=1 width=221) (actual time=96.624..107.727 rows=5 loops=1)

  • Filter: (h.finished IS NULL)
16. 107.674 107.674 ↑ 2.4 5 1

Seq Scan on so_reservings (cost=0.00..3,657.51 rows=12 width=164) (actual time=96.589..107.674 rows=5 loops=1)

  • Filter: (((sernr)::character varying)::text ~~* '%420201110%'::text)
  • Rows Removed by Filter: 116,081
17. 0.035 0.035 ↑ 1.0 1 5

Index Scan using so_header_pkey on so_header h (cost=0.29..7.97 rows=1 width=61) (actual time=0.007..0.007 rows=1 loops=5)

  • Index Cond: (sernr = so_reservings.sernr)
18. 0.040 0.040 ↑ 1.0 1 5

Index Scan using so_rows_pkey on so_rows r (cost=0.42..8.44 rows=1 width=9) (actual time=0.008..0.008 rows=1 loops=5)

  • Index Cond: (id = so_reservings.so_rows_id)
19. 0.020 0.020 ↑ 1.0 1 5

Index Scan using production_types_code_uidx on production_types p (cost=0.13..0.15 rows=1 width=64) (actual time=0.004..0.004 rows=1 loops=5)

  • Index Cond: (code = (h.prod_type)::text)
20. 0.040 0.040 ↑ 1.0 1 5

Index Scan using articles_code_idx on articles a_1 (cost=0.29..7.64 rows=1 width=9) (actual time=0.008..0.008 rows=1 loops=5)

  • Index Cond: (code = so_reservings.comp_part)
21. 0.175 0.175 ↓ 5.0 5 5

Index Scan using fki_structure_rows_structures_part_no_fkey on structure_rows sr (cost=0.29..0.76 rows=1 width=20) (actual time=0.014..0.035 rows=5 loops=5)

  • Index Cond: (part_no = h.part_no)
  • Filter: (NOT (alternate IS DISTINCT FROM h.alt_code))
  • Rows Removed by Filter: 74
Planning time : 2.734 ms
Execution time : 1,204.683 ms