explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dkgs

Settings
# exclusive inclusive rows x rows loops node
1. 1,577.426 6,762,801.110 ↓ 40,860.0 40,860 1

Sort (cost=30,411.65..30,411.65 rows=1 width=803) (actual time=6,762,769.516..6,762,801.110 rows=40,860 loops=1)

  • Sort Key: i.dateinvoiced DESC, (COALESCE(o.created, i.created)) DESC, o.documentno DESC, i.documentno DESC
  • Sort Method: external merge Disk: 8,896kB
2. 659.132 6,761,223.684 ↓ 40,860.0 40,860 1

Nested Loop Left Join (cost=21,291.91..30,411.64 rows=1 width=803) (actual time=2,961.843..6,761,223.684 rows=40,860 loops=1)

  • Join Filter: (vr.c_invoice_id = i.c_invoice_id)
3. 396.035 6,760,441.972 ↓ 40,860.0 40,860 1

Nested Loop Left Join (cost=21,282.71..30,402.40 rows=1 width=707) (actual time=2,961.814..6,760,441.972 rows=40,860 loops=1)

  • Join Filter: (i.c_invoice_id = c_voucher.c_invoice_id)
  • Rows Removed by Join Filter: 40,860
4. 249.752 6,759,882.497 ↓ 40,860.0 40,860 1

Nested Loop Left Join (cost=21,281.69..30,401.35 rows=1 width=675) (actual time=2,961.736..6,759,882.497 rows=40,860 loops=1)

5. 478.816 6,758,978.985 ↓ 40,860.0 40,860 1

Nested Loop (cost=21,281.55..30,401.18 rows=1 width=673) (actual time=2,961.680..6,758,978.985 rows=40,860 loops=1)

6. 518.464 6,756,497.980 ↓ 40,861.0 40,861 1

Nested Loop Left Join (cost=20,966.11..30,085.68 rows=1 width=654) (actual time=2,954.244..6,756,497.980 rows=40,861 loops=1)

  • Join Filter: (i.paymentrule = cp.paymentrule)
  • Rows Removed by Join Filter: 171,426
7. 821.063 6,754,794.547 ↓ 40,861.0 40,861 1

Nested Loop Left Join (cost=20,965.97..30,085.43 rows=1 width=521) (actual time=2,954.201..6,754,794.547 rows=40,861 loops=1)

8. 605,903.882 6,751,685.268 ↓ 40,861.0 40,861 1

Nested Loop Left Join (cost=20,965.83..30,085.25 rows=1 width=515) (actual time=2,954.159..6,751,685.268 rows=40,861 loops=1)

  • Join Filter: (payments.c_invoice_id = i.c_invoice_id)
  • Rows Removed by Join Filter: 1,496,704,579
9. 1,246.244 8,091.437 ↓ 40,861.0 40,861 1

Nested Loop Left Join (cost=7.92..6,658.80 rows=1 width=323) (actual time=0.201..8,091.437 rows=40,861 loops=1)

  • Filter: ((o.c_charge_id IS NULL) AND (COALESCE(o.ad_client_id, i.ad_client_id) = '1000000'::numeric) AND (COALESCE(o.isactive, i.isactive) = 'Y'::bpchar) AND (COALESCE(o.issotrx, i.issotrx) = 'Y'::bpchar) AND (COALESCE(o.ad_org_id, i.ad_org_id) = ANY ('{0,1000008,1000006,1000004,1000009,1000001,1000000,1000002,1000005,1000007,1000003,1000010}'::numeric[])))
  • Rows Removed by Filter: 26
10. 590.586 2,920.041 ↓ 18.3 40,887 1

Hash Join (cost=7.50..4,468.47 rows=2,237 width=150) (actual time=0.140..2,920.041 rows=40,887 loops=1)

  • Hash Cond: (i.c_doctype_id = dt.c_doctype_id)
11. 2,329.372 2,329.372 ↑ 1.0 47,420 1

Seq Scan on c_invoice i (cost=0.00..4,260.55 rows=47,479 width=156) (actual time=0.038..2,329.372 rows=47,420 loops=1)

  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (dateinvoiced >= to_date('2020-03-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (dateinvoiced <= to_date('2020-08-25 13:37:20'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Rows Removed by Filter: 9,004
12. 0.014 0.083 ↑ 1.0 9 1

Hash (cost=7.39..7.39 rows=9 width=6) (actual time=0.083..0.083 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.069 0.069 ↑ 1.0 9 1

Seq Scan on c_doctype dt (cost=0.00..7.39 rows=9 width=6) (actual time=0.008..0.069 rows=9 loops=1)

  • Filter: (docbasetype = 'ARI'::bpchar)
  • Rows Removed by Filter: 182
14. 3,925.152 3,925.152 ↑ 1.0 1 40,887

Index Scan using c_order_pkey on c_order o (cost=0.42..0.95 rows=1 width=207) (actual time=0.088..0.096 rows=1 loops=40,887)

  • Index Cond: (i.c_order_id = c_order_id)
15. 5,531,394.431 6,137,689.949 ↓ 1.1 36,630 40,861

GroupAggregate (cost=20,957.91..22,649.20 rows=34,545 width=144) (actual time=0.080..150.209 rows=36,630 loops=40,861)

  • Group Key: payments.c_invoice_id
16. 603,508.499 606,295.518 ↓ 1.0 41,539 40,861

Sort (cost=20,957.91..21,061.37 rows=41,383 width=144) (actual time=0.073..14.838 rows=41,539 loops=40,861)

  • Sort Key: payments.c_invoice_id
  • Sort Method: external sort Disk: 1,776kB
17. 7.781 2,787.019 ↓ 1.0 41,539 1

Subquery Scan on payments (cost=0.00..14,813.01 rows=41,383 width=144) (actual time=0.216..2,787.019 rows=41,539 loops=1)

18. 2,779.238 2,779.238 ↓ 1.0 41,539 1

Seq Scan on c_payment p (cost=0.00..14,399.18 rows=41,383 width=58) (actual time=0.215..2,779.238 rows=41,539 loops=1)

  • Filter: ((ad_client_id = '1000000'::numeric) AND (dateacct >= to_date('2020-03-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (dateacct <= to_date('2020-08-25 13:37:20'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Rows Removed by Filter: 6,804
19. 2,288.216 2,288.216 ↓ 0.0 0 40,861

Index Scan using ad_user_pkey on ad_user ad (cost=0.14..0.17 rows=1 width=15) (actual time=0.056..0.056 rows=0 loops=40,861)

  • Index Cond: (ad_user_id = o.ref_user1_id)
  • Filter: (ad_user_id <> '0'::numeric)
  • Rows Removed by Filter: 1
20. 1,184.969 1,184.969 ↓ 5.0 5 40,861

Index Scan using c_paymenttype_uq_name on c_paymenttype cp (cost=0.14..0.24 rows=1 width=162) (actual time=0.026..0.029 rows=5 loops=40,861)

  • Index Cond: (ad_client_id = o.ad_client_id)
21. 1,994.878 2,002.189 ↑ 1.0 1 40,861

Index Scan using c_bpartner_pkey on c_bpartner bp (cost=315.44..315.48 rows=1 width=33) (actual time=0.046..0.049 rows=1 loops=40,861)

  • Index Cond: (c_bpartner_id = COALESCE(o.c_bpartner_id, i.c_bpartner_id))
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 0
22.          

SubPlan (for Index Scan)

23. 7.311 7.311 ↑ 191.6 11 1

Seq Scan on c_bpartner (cost=0.00..309.89 rows=2,108 width=7) (actual time=0.130..7.311 rows=11 loops=1)

  • Filter: ((ad_orgbp_id <> '0'::numeric) AND (ad_client_id = '1000000'::numeric))
  • Rows Removed by Filter: 2,116
24. 653.760 653.760 ↑ 1.0 1 40,860

Index Scan using ad_user_pkey on ad_user u (cost=0.14..0.16 rows=1 width=15) (actual time=0.016..0.016 rows=1 loops=40,860)

  • Index Cond: (COALESCE(o.salesrep_id, o.salesrep_id) = ad_user_id)
25. 163.400 163.440 ↑ 1.0 1 40,860

HashAggregate (cost=1.01..1.03 rows=1 width=48) (actual time=0.004..0.004 rows=1 loops=40,860)

  • Group Key: c_voucher.c_invoice_id
26. 0.040 0.040 ↑ 1.0 1 1

Seq Scan on c_voucher (cost=0.00..1.01 rows=1 width=48) (actual time=0.039..0.040 rows=1 loops=1)

27. 122.563 122.580 ↓ 0.0 0 40,860

HashAggregate (cost=9.20..9.21 rows=1 width=56) (actual time=0.003..0.003 rows=0 loops=40,860)

  • Group Key: vr.c_invoice_id
28. 0.010 0.017 ↓ 0.0 0 1

Nested Loop (cost=0.14..9.18 rows=1 width=56) (actual time=0.017..0.017 rows=0 loops=1)

29. 0.002 0.002 ↑ 1.0 1 1

Seq Scan on c_voucher vo (cost=0.00..1.01 rows=1 width=24) (actual time=0.001..0.002 rows=1 loops=1)

30. 0.005 0.005 ↓ 0.0 0 1

Index Scan using c_voucherid on c_voucher_redeem vr (cost=0.14..8.16 rows=1 width=64) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (c_voucher_id = vo.c_voucher_id)