explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a2wG : Explain1

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 943,904.435 ↑ 2.0 1 1

Sort (cost=1,251,405.40..1,251,405.40 rows=2 width=363) (actual time=943,904.435..943,904.435 rows=1 loops=1)

  • Sort Key: (paymenttermduedate(inv.c_paymentterm_id, (inv.dateinvoiced)::timestamp with time zone)) DESC, (paymenttermduedays(inv.c_paymentterm_id, (inv.dateinvoiced)::timestamp with time zone, now()))
  • Sort Method: quicksort Memory: 25kB
2. 4.782 943,904.401 ↑ 2.0 1 1

Group (cost=1,251,402.80..1,251,405.39 rows=2 width=363) (actual time=943,904.400..943,904.401 rows=1 loops=1)

  • Group Key: inv.c_invoice_id, ord.documentno, cr.iso_code, pt.c_paymentterm_id, bp.c_bpartner_id, ord.poreference, u.name, pbp.name, ips.c_invoicepayschedule_id
3. 0.028 943,899.619 ↑ 2.0 1 1

Sort (cost=1,251,402.80..1,251,402.81 rows=2 width=363) (actual time=943,899.619..943,899.619 rows=1 loops=1)

  • Sort Key: inv.c_invoice_id, ord.documentno, cr.iso_code, pt.c_paymentterm_id, bp.c_bpartner_id, ord.poreference, u.name, pbp.name, ips.c_invoicepayschedule_id
  • Sort Method: quicksort Memory: 25kB
4. 0.003 943,899.591 ↑ 2.0 1 1

Nested Loop Left Join (cost=32,142.83..1,251,402.79 rows=2 width=363) (actual time=854,621.402..943,899.591 rows=1 loops=1)

5. 0.003 943,899.582 ↑ 2.0 1 1

Nested Loop Left Join (cost=32,142.41..1,251,401.75 rows=2 width=355) (actual time=854,621.393..943,899.582 rows=1 loops=1)

6. 1.433 943,895.703 ↑ 2.0 1 1

Hash Left Join (cost=32,141.98..1,251,399.36 rows=2 width=335) (actual time=854,617.515..943,895.703 rows=1 loops=1)

  • Hash Cond: (inv.pay_bpartner_id = pbp.c_bpartner_id)
  • Filter: ((inv.c_bpartner_id = '1005821'::numeric) OR (pbp.c_bpartner_id = '1005821'::numeric))
  • Rows Removed by Filter: 740
7. 0.727 943,839.420 ↑ 62.8 741 1

Hash Left Join (cost=23,060.77..1,242,143.52 rows=46,565 width=343) (actual time=9,439.984..943,839.420 rows=741 loops=1)

  • Hash Cond: (bp.salesrep_id = u.ad_user_id)
8. 1.290 943,831.564 ↑ 62.8 741 1

Hash Join (cost=22,133.31..1,241,041.42 rows=46,565 width=340) (actual time=9,432.766..943,831.564 rows=741 loops=1)

  • Hash Cond: (inv.c_paymentterm_id = pt.c_paymentterm_id)
9. 1.110 943,830.207 ↑ 62.8 741 1

Hash Join (cost=22,129.60..1,240,397.44 rows=46,565 width=122) (actual time=9,432.691..943,830.207 rows=741 loops=1)

  • Hash Cond: (inv.c_currency_id = cr.c_currency_id)
10. 1.490 943,829.032 ↑ 62.8 741 1

Hash Join (cost=22,122.68..1,239,750.25 rows=46,565 width=118) (actual time=9,432.617..943,829.032 rows=741 loops=1)

  • Hash Cond: (bpl.c_location_id = loc.c_location_id)
11. 2.472 943,623.099 ↑ 62.8 741 1

Hash Join (cost=15,948.00..1,232,935.30 rows=46,565 width=126) (actual time=9,227.841..943,623.099 rows=741 loops=1)

  • Hash Cond: (inv.c_bpartner_location_id = bpl.c_bpartner_location_id)
12. 1.600 943,415.730 ↑ 62.8 741 1

Hash Join (cost=9,085.83..1,225,432.86 rows=46,565 width=125) (actual time=9,021.621..943,415.730 rows=741 loops=1)

  • Hash Cond: (inv.c_bpartner_id = bp.c_bpartner_id)
13. 1.498 943,032.832 ↑ 62.8 741 1

Hash Join (cost=4.62..1,215,711.39 rows=46,565 width=91) (actual time=8,639.968..943,032.832 rows=741 loops=1)

  • Hash Cond: (inv.c_doctype_id = d.c_doctype_id)
14. 943,031.304 943,031.304 ↑ 958.3 741 1

Seq Scan on c_invoice inv (cost=0.00..1,212,578.21 rows=710,109 width=98) (actual time=8,639.933..943,031.304 rows=741 loops=1)

  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_client_id = '1000000'::numeric) AND (issotrx = 'Y'::bpchar) AND (invoiceopen(c_invoice_id, '0'::numeric) <> '0'::numeric) AND (paymenttermduedate(c_paymentterm_id, (dateinvoiced)::timestamp with time zone) < to_timestamp('2020-09-09'::text, 'YYYY-MM-DD'::text)))
  • Rows Removed by Filter: 2,158,416
15. 0.003 0.030 ↓ 1.1 9 1

Hash (cost=4.53..4.53 rows=8 width=6) (actual time=0.030..0.030 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.027 0.027 ↓ 1.1 9 1

Seq Scan on c_doctype d (cost=0.00..4.53 rows=8 width=6) (actual time=0.006..0.027 rows=9 loops=1)

  • Filter: (docbasetype = ANY ('{ARI,ARC}'::bpchar[]))
  • Rows Removed by Filter: 113
17. 46.242 381.298 ↓ 1.0 119,096 1

Hash (cost=7,601.09..7,601.09 rows=118,409 width=34) (actual time=381.298..381.298 rows=119,096 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 8,346kB
18. 335.056 335.056 ↓ 1.0 119,096 1

Seq Scan on c_bpartner bp (cost=0.00..7,601.09 rows=118,409 width=34) (actual time=0.005..335.056 rows=119,096 loops=1)

19. 33.876 204.897 ↓ 1.0 125,820 1

Hash (cost=5,325.41..5,325.41 rows=122,941 width=16) (actual time=204.897..204.897 rows=125,820 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 7,141kB
20. 171.021 171.021 ↓ 1.0 125,820 1

Seq Scan on c_bpartner_location bpl (cost=0.00..5,325.41 rows=122,941 width=16) (actual time=0.006..171.021 rows=125,820 loops=1)

21. 37.937 204.443 ↓ 1.0 126,774 1

Hash (cost=4,625.97..4,625.97 rows=123,897 width=8) (actual time=204.443..204.443 rows=126,774 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,085kB
22. 166.506 166.506 ↓ 1.0 126,774 1

Seq Scan on c_location loc (cost=0.00..4,625.97 rows=123,897 width=8) (actual time=0.003..166.506 rows=126,774 loops=1)

23. 0.028 0.065 ↑ 1.0 174 1

Hash (cost=4.74..4.74 rows=174 width=9) (actual time=0.065..0.065 rows=174 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
24. 0.037 0.037 ↑ 1.0 174 1

Seq Scan on c_currency cr (cost=0.00..4.74 rows=174 width=9) (actual time=0.008..0.037 rows=174 loops=1)

25. 0.023 0.067 ↑ 1.0 76 1

Hash (cost=2.76..2.76 rows=76 width=218) (actual time=0.067..0.067 rows=76 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
26. 0.044 0.044 ↑ 1.0 76 1

Seq Scan on c_paymentterm pt (cost=0.00..2.76 rows=76 width=218) (actual time=0.018..0.044 rows=76 loops=1)

27. 3.458 7.129 ↓ 1.0 19,478 1

Hash (cost=685.54..685.54 rows=19,354 width=16) (actual time=7.129..7.129 rows=19,478 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,207kB
28. 3.671 3.671 ↓ 1.0 19,478 1

Seq Scan on ad_user u (cost=0.00..685.54 rows=19,354 width=16) (actual time=0.009..3.671 rows=19,478 loops=1)

29. 27.083 54.850 ↓ 1.0 119,096 1

Hash (cost=7,601.09..7,601.09 rows=118,409 width=16) (actual time=54.850..54.850 rows=119,096 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,792kB
30. 27.767 27.767 ↓ 1.0 119,096 1

Seq Scan on c_bpartner pbp (cost=0.00..7,601.09 rows=118,409 width=16) (actual time=0.004..27.767 rows=119,096 loops=1)

31. 3.876 3.876 ↑ 1.0 1 1

Index Scan using c_order_pkey on c_order ord (cost=0.43..1.19 rows=1 width=28) (actual time=3.876..3.876 rows=1 loops=1)

  • Index Cond: (inv.c_order_id = c_order_id)
32. 0.006 0.006 ↓ 0.0 0 1

Index Scan using c_invoicepayschedule_invoice on c_invoicepayschedule ips (cost=0.42..0.50 rows=2 width=16) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (inv.c_invoice_id = c_invoice_id)