explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1MgR

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 146,021.121 ↑ 258.3 153 1

Sort (cost=3,011,167.36..3,011,266.15 rows=39,517 width=249) (actual time=146,021.112..146,021.121 rows=153 loops=1)

  • Sort Key: "*SELECT* 1".c_bpartner_id, "*SELECT* 1".c_currency_id, "*SELECT* 1".c_invoice_id
  • Sort Method: quicksort Memory: 46kB
  • "*SELECT* 1".c_bpartner_id, "*SELECT* 1".issotrx, "*SELECT* 1".dateinvoiced, "*SELECT* 1".dateacct, "*SELECT* 1".netdays, "*SELECT* 1".duedate, "*SELECT* 1".
2. 82.070 146,020.377 ↑ 258.3 153 1

Nested Loop (cost=2,980,197.45..3,003,421.70 rows=39,517 width=249) (actual time=145,955.597..146,020.377 rows=153 loops=1)

3. 0.009 0.009 ↑ 1.0 1 1

Index Scan using c_bpartner_pkey on c_bpartner bp (cost=0.29..8.30 rows=1 width=13) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '1000016'::numeric)
4. 0.167 145,938.298 ↑ 258.3 153 1

Unique (cost=2,980,197.16..2,982,765.77 rows=39,517 width=257) (actual time=145,938.084..145,938.298 rows=153 loops=1)

5. 0.937 145,938.131 ↑ 258.3 153 1

Sort (cost=2,980,197.16..2,980,295.95 rows=39,517 width=257) (actual time=145,938.083..145,938.131 rows=153 loops=1)

  • Sort Key: "*SELECT* 1".ad_org_id, "*SELECT* 1".ad_client_id, "*SELECT* 1".documentno, "*SELECT* 1".c_invoice_id, "*SELECT* 1".c_order_id,
  • Sort Method: quicksort Memory: 65kB
6. 5,805.978 145,937.194 ↑ 258.3 153 1

Append (cost=1,373.18..2,972,315.00 rows=39,517 width=257) (actual time=2,988.357..145,937.194 rows=153 loops=1)

  • -> Subquery Scan on "*SELECT* 1" (cost=1373.18..2537486.76 rows=39516 width=257) (actual time=2988.356..140131.400 rows=153 loops
7. 19.021 140,131.216 ↑ 258.3 153 1

Hash Join (cost=1,373.18..2,537,091.60 rows=39,516 width=257) (actual time=2,988.355..140,131.216 rows=153 loops=1)

  • Hash Cond: (i.c_paymentterm_id = p.c_paymentterm_id)
8. 4.975 140,112.195 ↑ 258.3 153 1

Nested Loop (cost=1,371.84..2,496,043.01 rows=39,516 width=180) (actual time=2,986.306..140,112.195 rows=153 loops=1)

  • -> Hash Join (cost=1371.56..2441383.57 rows=39516 width=180) (actual time=2986.222..140107.949 rows=153 loops=1
  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
  • -> Hash Join (cost=1365.92..2440834.60 rows=39516 width=182) (actual time=2986.161..140107.629 rows=153 l
  • Hash Cond: (i.c_bpartner_location_id = bpl.c_bpartner_location_id)
9. 140,090.673 140,090.673 ↑ 258.3 153 1

Seq Scan on c_invoice i (cost=0.00..2,438,925.33 rows=39,516 width=189) (actual time=2,969.602..140,090.673 rows=153 loops=1)

  • Filter: ((ispayschedulevalid <> 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_client_id = ANY ('{0,1000000}'::numeric[])) AND (COALESCE(pay_bpartner_id, c_bpartner_id) = '1000016'::numeric) AND (invoiceopentodate(c_invoice_id, NULL::numeric, '2019-11-01 23:59:59+02'::timestamp with time zone) <> '0'::numeric))
  • Rows Removed by Filter: 7944344
10. 16.547 16.547 ↑ 1.0 14,523 1

Hash (cost=1,182.46..1,182.46 rows=14,677 width=8) (actual time=16.547..16.547 rows=14,523 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 700kB
11. 0.000 14.222 ↑ 1.0 14,523 1

Hash Join (cost=576.88..1,182.46 rows=14,677 width=8) (actual time=5.036..14.222 rows=14,523 loops=1)

  • Hash Cond: (bpl.c_location_id = loc.c_location_id)
12. 2.597 2.597 ↑ 1.0 14,523 1

Seq Scan on c_bpartner_location bpl (cost=0.00..403.77 rows=14,677 width=16) (actual time=0.003..2.597 rows=14,523 loops=1)

13. 2.224 5.020 ↑ 1.0 14,973 1

Hash (cost=389.17..389.17 rows=15,017 width=8) (actual time=5.020..5.020 rows=14,973 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 717kB
14. 2.796 2.796 ↑ 1.0 14,973 1

Seq Scan on c_location loc (cost=0.00..389.17 rows=15,017 width=8) (actual time=0.003..2.796 rows=14,973 loops=1)

15. 0.025 0.053 ↑ 1.0 117 1

Hash (cost=4.17..4.17 rows=117 width=10) (actual time=0.053..0.053 rows=117 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
16. 0.028 0.028 ↑ 1.0 117 1

Seq Scan on c_doctype d (cost=0.00..4.17 rows=117 width=10) (actual time=0.003..0.028 rows=117 loops=1)

17. 1.224 1.224 ↑ 1.0 1 153

Index Only Scan using c_bpartner_pkey on c_bpartner b (cost=0.29..0.35 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=153)

  • Index Cond: (c_bpartner_id = i.c_bpartner_id)
  • Heap Fetches: 153
18. 0.010 0.018 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=80) (actual time=0.018..0.018 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.008 0.008 ↑ 1.0 15 1

Seq Scan on c_paymentterm p (cost=0.00..1.15 rows=15 width=80) (actual time=0.006..0.008 rows=15 loops=1)

20. 0.000 5,805.730 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..434,828.25 rows=1 width=257) (actual time=5,805.730..5,805.730 rows=0 loops=1)

21. 0.001 5,805.730 ↓ 0.0 0 1

Nested Loop (cost=0.00..434,828.24 rows=1 width=257) (actual time=5,805.730..5,805.730 rows=0 loops=1)

  • Join Filter: ((i_1.c_invoice_id = ips.c_invoice_id) AND (invoiceopentodate(i_1.c_invoice_id, ips.c_invoicepayschedule_id, '2019-11-01 23:59:59+02'::timestamp with time zone) <> '0'::numeric))
22. 0.001 5,805.729 ↓ 0.0 0 1

Nested Loop (cost=0.00..434,826.18 rows=1 width=180) (actual time=5,805.729..5,805.729 rows=0 loops=1)

  • Join Filter: (bpl_1.c_location_id = loc_1.c_location_id)
23. 0.000 5,805.728 ↓ 0.0 0 1

Nested Loop (cost=0.00..434,248.27 rows=1 width=188) (actual time=5,805.728..5,805.728 rows=0 loops=1)

  • Join Filter: (i_1.c_bpartner_location_id = bpl_1.c_bpartner_location_id)
24. 0.001 5,805.728 ↓ 0.0 0 1

Nested Loop (cost=0.00..433,661.04 rows=1 width=187) (actual time=5,805.728..5,805.728 rows=0 loops=1)

  • Join Filter: (i_1.c_bpartner_id = b_1.c_bpartner_id)
25. 5,805.727 5,805.727 ↓ 0.0 0 1

Nested Loop (cost=0.00..432,929.81 rows=1 width=187) (actual time=5,805.727..5,805.727 rows=0 loops=1)

  • Join Filter: (i_1.c_doctype_id = d_1.c_doctype_id)
26. 5,805.726 5,805.726 ↓ 0.0 0 1

Seq Scan on c_invoice i_1 (cost=0.00..432,924.18 rows=1 width=189) (actual time=5,805.726..5,805.726 rows=0 loops=1)

  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_client_id = ANY ('{0,1000000}'::numeric[])) AND (ispayschedulevalid = 'Y'::bpchar) AND (COALESCE(pay_bpartner_id, c_bpartner_id) = '1000016'::numeric))
  • Rows Removed by Filter: 7944497
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_doctype d_1 (cost=0.00..4.17 rows=117 width=10) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_bpartner b_1 (cost=0.00..582.21 rows=11,921 width=8) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_bpartner_location bpl_1 (cost=0.00..403.77 rows=14,677 width=16) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_location loc_1 (cost=0.00..389.17 rows=15,017 width=8) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_invoicepayschedule ips (cost=0.00..1.02 rows=1 width=112) (never executed)

  • Filter: (isvalid = 'Y'::bpchar)