explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D4N1

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

Limit (cost=32,214.02..32,214.03 rows=1 width=288) (actual time=5,385.271..5,385.274 rows=5 loops=1)

2. 0.012 5,385.271 ↓ 5.0 5 1

Sort (cost=32,214.02..32,214.03 rows=1 width=288) (actual time=5,385.269..5,385.271 rows=5 loops=1)

  • Sort Key: so_reportings_rows.sernr, so_reportings_rows.id
  • Sort Method: quicksort Memory: 26kB
3. 0.006 5,385.259 ↓ 5.0 5 1

Subquery Scan on so_reportings_rows (cost=32,214.00..32,214.01 rows=1 width=288) (actual time=5,385.255..5,385.259 rows=5 loops=1)

4. 0.012 5,385.253 ↓ 5.0 5 1

Sort (cost=32,214.00..32,214.00 rows=1 width=324) (actual time=5,385.251..5,385.253 rows=5 loops=1)

  • Sort Key: so_reservings.sernr, sr.rowno
  • Sort Method: quicksort Memory: 26kB
5. 0.022 5,385.241 ↓ 5.0 5 1

GroupAggregate (cost=32,213.94..32,213.99 rows=1 width=324) (actual time=5,385.230..5,385.241 rows=5 loops=1)

  • Group Key: so_reservings.id, h.part_no, h.part_desc, h.batch, h.prod_type, a.class, r.qty_pa, p.from_warehouse, sr.rowno, h.start_date, h.prod_comment, h.finished
6. 0.033 5,385.219 ↓ 5.0 5 1

Sort (cost=32,213.94..32,213.94 rows=1 width=294) (actual time=5,385.217..5,385.219 rows=5 loops=1)

  • Sort Key: so_reservings.id, h.part_no, h.part_desc, h.batch, h.prod_type, a.class, r.qty_pa, p.from_warehouse, sr.rowno, h.start_date, h.prod_comment, h.finished
  • Sort Method: quicksort Memory: 26kB
7. 0.029 5,385.186 ↓ 5.0 5 1

Nested Loop Left Join (cost=18,447.31..32,213.93 rows=1 width=294) (actual time=1,241.984..5,385.186 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
8. 0.022 5,384.937 ↓ 5.0 5 1

Nested Loop Left Join (cost=18,447.02..32,213.15 rows=1 width=292) (actual time=1,241.956..5,384.937 rows=5 loops=1)

9. 0.033 5,384.865 ↓ 5.0 5 1

Nested Loop Left Join (cost=18,446.74..32,205.51 rows=1 width=290) (actual time=1,241.940..5,384.865 rows=5 loops=1)

10. 171.034 5,384.767 ↓ 5.0 5 1

Nested Loop Left Join (cost=18,446.61..32,205.35 rows=1 width=258) (actual time=1,241.915..5,384.767 rows=5 loops=1)

  • Join Filter: ((r_1.sernr = so_reservings.sernr) AND (r_1.comp_part = so_reservings.comp_part))
  • Rows Removed by Join Filter: 610,883
11. 0.020 107.653 ↓ 5.0 5 1

Nested Loop Left Join (cost=0.70..3,761.72 rows=1 width=226) (actual time=96.671..107.653 rows=5 loops=1)

12. 0.037 107.603 ↓ 5.0 5 1

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

  • Filter: (h.finished IS NULL)
13. 107.516 107.516 ↑ 2.4 5 1

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

  • Filter: (((sernr)::character varying)::text ~~* '%420201110%'::text)
  • Rows Removed by Filter: 116,081
14. 0.050 0.050 ↑ 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.010..0.010 rows=1 loops=5)

  • Index Cond: (sernr = so_reservings.sernr)
15. 0.030 0.030 ↑ 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.006..0.006 rows=1 loops=5)

  • Index Cond: (id = so_reservings.so_rows_id)
16. 4,637.780 5,106.080 ↓ 5.0 122,177 5

GroupAggregate (cost=18,445.90..27,834.46 rows=24,367 width=72) (actual time=46.924..1,021.216 rows=122,177 loops=5)

  • Group Key: r_1.sernr, r_1.comp_part, r_1.alt_code, r_1.batch, r_1.fin_warehouse, r_1.location, r_1.so_rows_id
17. 412.543 468.300 ↑ 1.0 129,826 5

Sort (cost=18,445.90..18,771.60 rows=130,280 width=45) (actual time=46.894..93.660 rows=129,826 loops=5)

  • Sort Key: r_1.sernr, r_1.comp_part, r_1.alt_code, r_1.batch, r_1.fin_warehouse, r_1.location, r_1.so_rows_id
  • Sort Method: external sort Disk: 6,520kB
18. 55.757 55.757 ↑ 1.0 129,826 1

Seq Scan on so_reportings r_1 (cost=0.00..3,366.80 rows=130,280 width=45) (actual time=0.007..55.757 rows=129,826 loops=1)

19. 0.065 0.065 ↑ 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.013..0.013 rows=1 loops=5)

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

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

  • Index Cond: (code = so_reservings.comp_part)
21. 0.220 0.220 ↓ 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.018..0.044 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.509 ms
Execution time : 5,386.909 ms