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".daysdue, (("*SELECT* 1".discountdate)::timestamp without time zone), "*SELECT* 1".discountamt, "*SELECT* 1".grandtotal, "*SELECT* 1".c_currency_id, "*SELECT* 1".c_conversiontype_id, "*SELECT* 1".c_paymentterm_id, "*SELECT* 1".ispayschedulevalid, (NULL::numeric), "*SELECT* 1".invoicecollectiontype, "*SELECT* 1".c_campaign_id, "*SELECT* 1".c_project_id, "*SELECT* 1".c_activity_id, "*SELECT* 1".pay_bpartner_id
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. 0.064 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)

7. 0.184 140,131.400 ↑ 258.3 153 1

Subquery Scan on *SELECT* 1 (cost=1,373.18..2,537,486.76 rows=39,516 width=257) (actual time=2,988.356..140,131.400 rows=153 loops=1)

8. 19.003 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)
9. 3.022 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)

10. 0.267 140,107.949 ↑ 258.3 153 1

Hash Join (cost=1,371.56..2,441,383.57 rows=39,516 width=180) (actual time=2,986.222..140,107.949 rows=153 loops=1)

  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
11. 0.409 140,107.629 ↑ 258.3 153 1

Hash Join (cost=1,365.92..2,440,834.60 rows=39,516 width=182) (actual time=2,986.161..140,107.629 rows=153 loops=1)

  • Hash Cond: (i.c_bpartner_location_id = bpl.c_bpartner_location_id)
12. 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
13. 2.325 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)

14. 6.605 14.222 ↑ 1.0 14,523 1

Buckets: 16384 Batches: 1 Memory Usage: 700kB-> 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)
15. 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)

16. 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
17. 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)

18. 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
19. 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)

20. 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
21. 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
22. 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)

23. 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)

24. 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))
25. 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)
26. 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)
27. 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)
28. 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)
29. 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
30. 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)

31. 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)

32. 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)

33. 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)

34. 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)