explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UPtb

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 79,087.239 ↑ 1.0 1 1

Limit (cost=41,706.39..41,706.47 rows=1 width=842) (actual time=79,087.236..79,087.239 rows=1 loops=1)

2. 0.029 79,087.233 ↑ 1.0 1 1

Result (cost=41,706.39..41,706.47 rows=1 width=842) (actual time=79,087.232..79,087.233 rows=1 loops=1)

3. 0.018 79,087.204 ↑ 1.0 1 1

Sort (cost=41,706.39..41,706.40 rows=1 width=834) (actual time=79,087.203..79,087.204 rows=1 loops=1)

  • Sort Key: o.aldatum DESC, o.alzeit DESC, o.customerid
  • Sort Method: quicksort Memory: 25kB
4. 0.076 79,087.186 ↑ 1.0 1 1

GroupAggregate (cost=41,706.29..41,706.38 rows=1 width=834) (actual time=79,087.186..79,087.186 rows=1 loops=1)

  • Group Key: o.orderid, x.f1beda, x.f1zacd, x.f1tour, (COALESCE((s1.s1renr)::character varying, 'N/A'::character varying)), (COALESCE((s1.s1reda)::character varying, 'N/A'::character varying)), x.f1bekz, (COALESCE(f1.f1benr)), (COALESCE((o.lsubsidiary)::character varying, ''::character varying)), (COALESCE(o.kommityp, ''::character varying)), (COALESCE(o.vorgangsart, ''::character varying)), (COALESCE(o.kommifiliale, ''::character varying)), x.f1inda, x.f1inti, x.f1finr, x.f1anda, x.f1time
5. 0.090 79,087.110 ↓ 8.0 8 1

Sort (cost=41,706.29..41,706.29 rows=1 width=439) (actual time=79,087.104..79,087.110 rows=8 loops=1)

  • Sort Key: o.orderid, x.f1beda, x.f1zacd, x.f1tour, (COALESCE((s1.s1renr)::character varying, 'N/A'::character varying)), (COALESCE((s1.s1reda)::character varying, 'N/A'::character varying)), x.f1bekz, (COALESCE(f1.f1benr)), (COALESCE((o.lsubsidiary)::character varying, ''::character varying)), (COALESCE(o.kommityp, ''::character varying)), (COALESCE(o.vorgangsart, ''::character varying)), (COALESCE(o.kommifiliale, ''::character varying)), x.f1inda, x.f1inti, x.f1finr, x.f1anda, x.f1time
  • Sort Method: quicksort Memory: 29kB
6. 336.544 79,087.020 ↓ 8.0 8 1

Nested Loop (cost=33,314.91..41,706.28 rows=1 width=439) (actual time=32,808.838..79,087.020 rows=8 loops=1)

7. 990.092 68,825.015 ↓ 28,277.7 254,499 1

Nested Loop (cost=33,314.49..41,697.69 rows=9 width=248) (actual time=6,025.620..68,825.015 rows=254,499 loops=1)

8. 1,405.095 50,812.803 ↓ 31,522.4 283,702 1

Nested Loop Left Join (cost=33,314.07..41,691.93 rows=9 width=252) (actual time=3,161.523..50,812.803 rows=283,702 loops=1)

  • Join Filter: (((CASE WHEN ((f1.f1zacd)::text = 'VOK'::text) THEN f1.f1benr ELSE s1.s1renr END)::character varying)::text = (o5.o500_belegnr1)::text)
  • Rows Removed by Join Filter: 268,473
9. 1,010.510 31,820.106 ↓ 141,835.5 283,671 1

Nested Loop Left Join (cost=33,314.07..41,689.72 rows=2 width=262) (actual time=3,161.467..31,820.106 rows=283,671 loops=1)

10. 382.452 24,776.335 ↓ 154,699.0 154,699 1

Nested Loop Left Join (cost=33,313.65..41,688.94 rows=1 width=248) (actual time=3,161.437..24,776.335 rows=154,699 loops=1)

11. 679.526 20,602.375 ↓ 135,411.0 135,411 1

Nested Loop Left Join (cost=33,313.23..41,688.37 rows=1 width=248) (actual time=3,161.418..20,602.375 rows=135,411 loops=1)

12. 535.994 11,798.189 ↓ 135,411.0 135,411 1

Nested Loop Left Join (cost=33,312.81..41,687.63 rows=1 width=232) (actual time=3,156.213..11,798.189 rows=135,411 loops=1)

13. 1,174.337 5,304.111 ↓ 135,411.0 135,411 1

Hash Right Join (cost=33,312.39..41,686.85 rows=1 width=219) (actual time=3,156.186..5,304.111 rows=135,411 loops=1)

  • Hash Cond: ((paya.account)::numeric = o.orderid)
14. 1,062.038 1,062.038 ↑ 1.0 137,806 1

Seq Scan on payment paya (cost=0.00..7,679.30 rows=139,030 width=4) (actual time=0.020..1,062.038 rows=137,806 loops=1)

15. 698.381 3,067.736 ↓ 135,411.0 135,411 1

Hash (cost=33,312.38..33,312.38 rows=1 width=219) (actual time=3,067.736..3,067.736 rows=135,411 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 16 (originally 1) Memory Usage: 3,969kB
16. 1,069.141 2,369.355 ↓ 135,411.0 135,411 1

Hash Join (cost=14,244.80..33,312.38 rows=1 width=219) (actual time=882.443..2,369.355 rows=135,411 loops=1)

  • Hash Cond: ((o.customerid = x.f1klnr) AND (o.orderid = x.f1benr))
17. 434.429 434.429 ↑ 1.0 135,411 1

Seq Scan on "order" o (cost=0.00..9,913.47 rows=139,747 width=175) (actual time=0.013..434.429 rows=135,411 loops=1)

18. 239.732 865.785 ↓ 1.0 135,411 1

Hash (cost=10,994.36..10,994.36 rows=131,229 width=56) (actual time=865.785..865.785 rows=135,411 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,554kB
19. 626.053 626.053 ↓ 1.0 135,411 1

Seq Scan on f1ex x (cost=0.00..10,994.36 rows=131,229 width=56) (actual time=0.020..626.053 rows=135,411 loops=1)

  • Filter: ((f1klkz)::text = '1'::text)
20. 5,958.084 5,958.084 ↑ 1.0 1 135,411

Index Scan using idx_s1_s1exnr_s1klnr on s1 (cost=0.42..0.76 rows=1 width=25) (actual time=0.043..0.044 rows=1 loops=135,411)

  • Index Cond: ((s1exnr = o.orderid) AND (s1klnr = o.customerid))
  • Filter: ((s1klkz)::text = '1'::text)
21. 8,124.660 8,124.660 ↑ 1.0 1 135,411

Index Scan using idx_f1_f1exnr on f1 (cost=0.42..0.73 rows=1 width=22) (actual time=0.059..0.060 rows=1 loops=135,411)

  • Index Cond: (o.orderid = f1exnr)
  • Filter: (f1klnr = o.customerid)
22. 3,791.508 3,791.508 ↑ 1.0 1 135,411

Index Only Scan using idx_payment_orderpk on payment payo (cost=0.42..0.57 rows=1 width=6) (actual time=0.026..0.028 rows=1 loops=135,411)

  • Index Cond: (orderpk = o.orderid)
  • Heap Fetches: 78,454
23. 6,033.261 6,033.261 ↑ 1.0 2 154,699

Index Scan using idx_orderposition_s2 on orderposition op (cost=0.42..0.76 rows=2 width=20) (actual time=0.034..0.039 rows=2 loops=154,699)

  • Index Cond: (orderid = o.orderid)
24. 1,985.697 17,587.602 ↑ 2.5 2 283,671

Append (cost=0.00..1.00 rows=5 width=14) (actual time=0.039..0.062 rows=2 loops=283,671)

25. 283.671 283.671 ↓ 0.0 0 283,671

Seq Scan on o5 (cost=0.00..0.00 rows=1 width=92) (actual time=0.001..0.001 rows=0 loops=283,671)

  • Filter: ((o500_ktonr)::text = ((f1.f1klnr)::character varying)::text)
26. 10,495.827 10,495.827 ↑ 2.0 1 283,671

Index Scan using idx_o5_o500_ktonr2 on o5_live (cost=0.42..0.63 rows=2 width=14) (actual time=0.028..0.037 rows=1 loops=283,671)

  • Index Cond: ((o500_ktonr)::text = ((f1.f1klnr)::character varying)::text)
27. 4,822.407 4,822.407 ↓ 0.0 0 283,671

Index Scan using o5_archiv_s1 on o5_archiv (cost=0.29..0.37 rows=2 width=14) (actual time=0.016..0.017 rows=0 loops=283,671)

  • Index Cond: ((o500_ktonr)::text = ((f1.f1klnr)::character varying)::text)
28. 17,022.120 17,022.120 ↑ 1.0 1 283,702

Index Scan using logisticorder_order_id_idx on logisticorder lo (cost=0.42..0.63 rows=1 width=22) (actual time=0.059..0.060 rows=1 loops=283,702)

  • Index Cond: ((orderid)::text = (o.reference)::text)
29. 9,925.461 9,925.461 ↓ 0.0 0 254,499

Index Scan using logisticorderitem_order_id_idx on logisticorderitem li (cost=0.42..0.94 rows=1 width=19) (actual time=0.039..0.039 rows=0 loops=254,499)

  • Index Cond: (order_id = lo.id)
  • Filter: ((externalreferenceid)::text = '07800B60221'::text)
  • Rows Removed by Filter: 3
Planning time : 39.344 ms
Execution time : 79,087.701 ms