explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ILuK : Explain2

Settings
# exclusive inclusive rows x rows loops node
1. 188.697 1,908,969.043 ↓ 35,126.0 35,126 1

Sort (cost=52,017.89..52,017.90 rows=1 width=770) (actual time=1,908,965.155..1,908,969.043 rows=35,126 loops=1)

  • Sort Key: i.dateinvoiced DESC, (COALESCE(o.created, i.created)) DESC, o.documentno DESC, i.documentno DESC
  • Sort Method: quicksort Memory: 17,400kB
2. 152.108 1,908,780.346 ↓ 35,126.0 35,126 1

Nested Loop Left Join (cost=35,743.00..52,017.88 rows=1 width=770) (actual time=1,394.834..1,908,780.346 rows=35,126 loops=1)

  • Join Filter: (vr.c_invoice_id = i.c_invoice_id)
3. 117.537 1,908,593.112 ↓ 35,126.0 35,126 1

Nested Loop Left Join (cost=35,719.71..51,990.60 rows=1 width=674) (actual time=1,394.811..1,908,593.112 rows=35,126 loops=1)

  • Join Filter: (i.c_invoice_id = c_voucher.c_invoice_id)
  • Rows Removed by Join Filter: 35,126
4. 126.904 1,908,405.323 ↓ 35,126.0 35,126 1

Nested Loop Left Join (cost=35,711.13..51,975.01 rows=1 width=642) (actual time=1,393.815..1,908,405.323 rows=35,126 loops=1)

5. 146.364 1,907,997.411 ↓ 35,126.0 35,126 1

Nested Loop (cost=35,710.84..51,974.70 rows=1 width=641) (actual time=1,393.747..1,907,997.411 rows=35,126 loops=1)

6. 221.939 1,906,867.519 ↓ 35,126.0 35,126 1

Nested Loop Left Join (cost=27,221.30..43,484.20 rows=1 width=624) (actual time=1,130.613..1,906,867.519 rows=35,126 loops=1)

  • Join Filter: (i.paymentrule = cp.paymentrule)
  • Rows Removed by Join Filter: 177,932
7. 144,876.758 1,906,224.068 ↓ 35,126.0 35,126 1

Nested Loop Left Join (cost=27,221.16..43,483.94 rows=1 width=491) (actual time=1,130.379..1,906,224.068 rows=35,126 loops=1)

  • Join Filter: (payments.c_invoice_id = i.c_invoice_id)
  • Rows Removed by Join Filter: 1,172,086,675
8. 96.416 2,904.624 ↓ 35,126.0 35,126 1

Nested Loop Left Join (cost=5.73..13,666.59 rows=1 width=299) (actual time=4.070..2,904.624 rows=35,126 loops=1)

9. 244.931 2,527.200 ↓ 35,126.0 35,126 1

Nested Loop Left Join (cost=5.44..13,666.27 rows=1 width=294) (actual time=4.052..2,527.200 rows=35,126 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,1000000,1000038,1000001,1000002,1000003,1000004,1000005,1000010,1000013,1000014,1000015,1000016,1000017,1000018,1000019,1000021,1000022,1000023,1000024,1000025,1000026,1000027,1000028,1000029,1000030,1000031,1000032,1000034,1000035,1000036,1000037,1000050,1000051,1000052,1000020,1000006,1000007,1000008,1000009,1000033}'::numeric[])))
  • Rows Removed by Filter: 1
10. 127.352 771.808 ↓ 33.6 35,127 1

Hash Join (cost=5.01..7,222.41 rows=1,047 width=127) (actual time=2.610..771.808 rows=35,127 loops=1)

  • Hash Cond: (i.c_doctype_id = dt.c_doctype_id)
11. 644.413 644.413 ↓ 1.1 35,878 1

Index Scan using c_invoice_dateinvoiced on c_invoice i (cost=0.43..7,087.58 rows=31,942 width=134) (actual time=2.466..644.413 rows=35,878 loops=1)

  • Index Cond: ((dateinvoiced >= to_date('2020-08-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (dateinvoiced <= to_date('2020-08-31 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Filter: (docstatus = ANY ('{CO,CL}'::bpchar[]))
  • Rows Removed by Filter: 8
12. 0.005 0.043 ↓ 1.2 5 1

Hash (cost=4.53..4.53 rows=4 width=6) (actual time=0.043..0.043 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.038 0.038 ↓ 1.2 5 1

Seq Scan on c_doctype dt (cost=0.00..4.53 rows=4 width=6) (actual time=0.010..0.038 rows=5 loops=1)

  • Filter: (docbasetype = 'ARI'::bpchar)
  • Rows Removed by Filter: 117
14. 1,510.461 1,510.461 ↑ 1.0 1 35,127

Index Scan using c_order_pkey on c_order o (cost=0.43..6.09 rows=1 width=201) (actual time=0.040..0.043 rows=1 loops=35,127)

  • Index Cond: (i.c_order_id = c_order_id)
15. 281.008 281.008 ↓ 0.0 0 35,126

Index Scan using ad_user_pkey on ad_user ad (cost=0.29..0.31 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=35,126)

  • Index Cond: (ad_user_id = o.ref_user1_id)
  • Filter: (ad_user_id <> '0'::numeric)
  • Rows Removed by Filter: 1
16. 1,529,456.292 1,758,442.686 ↑ 1.2 33,369 35,126

GroupAggregate (cost=27,215.43..28,950.05 rows=38,547 width=144) (actual time=0.034..50.061 rows=33,369 loops=35,126)

  • Group Key: payments.c_invoice_id
17. 227,933.916 228,986.394 ↓ 1.1 41,628 35,126

Sort (cost=27,215.43..27,311.80 rows=38,547 width=144) (actual time=0.031..6.519 rows=41,628 loops=35,126)

  • Sort Key: payments.c_invoice_id
  • Sort Method: quicksort Memory: 4,789kB
18. 5.690 1,052.478 ↓ 1.1 41,628 1

Subquery Scan on payments (cost=0.43..24,279.24 rows=38,547 width=144) (actual time=3.435..1,052.478 rows=41,628 loops=1)

19. 1,046.788 1,046.788 ↓ 1.1 41,628 1

Index Scan using paydateacct on c_payment p (cost=0.43..23,893.77 rows=38,547 width=58) (actual time=3.434..1,046.788 rows=41,628 loops=1)

  • Index Cond: ((dateacct >= to_date('2020-08-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (dateacct <= to_date('2020-08-31 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Filter: (ad_client_id = '1000000'::numeric)
20. 421.512 421.512 ↓ 6.0 6 35,126

Index Scan using c_paymenttype_uq_name on c_paymenttype cp (cost=0.14..0.25 rows=1 width=162) (actual time=0.010..0.012 rows=6 loops=35,126)

  • Index Cond: (ad_client_id = o.ad_client_id)
21. 721.201 983.528 ↑ 1.0 1 35,126

Index Scan using c_bpartner_pkey on c_bpartner bp (cost=8,489.54..8,490.48 rows=1 width=31) (actual time=0.027..0.028 rows=1 loops=35,126)

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

SubPlan (for Index Scan)

23. 262.327 262.327 ↑ 2,753.4 43 1

Seq Scan on c_bpartner (cost=0.00..8,193.14 rows=118,396 width=8) (actual time=0.017..262.327 rows=43 loops=1)

  • Filter: ((ad_orgbp_id <> '0'::numeric) AND (ad_client_id = '1000000'::numeric))
  • Rows Removed by Filter: 119,053
24. 281.008 281.008 ↑ 1.0 1 35,126

Index Scan using ad_user_pkey on ad_user u (cost=0.29..0.31 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=35,126)

  • Index Cond: (COALESCE(o.salesrep_id, o.salesrep_id) = ad_user_id)
25. 69.355 70.252 ↑ 200.0 1 35,126

HashAggregate (cost=8.59..11.09 rows=200 width=21) (actual time=0.002..0.002 rows=1 loops=35,126)

  • Group Key: c_voucher.c_invoice_id
26. 0.897 0.897 ↑ 1.0 239 1

Seq Scan on c_voucher (cost=0.00..7.39 rows=239 width=21) (actual time=0.325..0.897 rows=239 loops=1)

27. 35.110 35.126 ↓ 0.0 0 35,126

HashAggregate (cost=23.29..25.04 rows=100 width=50) (actual time=0.001..0.001 rows=0 loops=35,126)

  • Group Key: vr.c_invoice_id
28. 0.012 0.016 ↓ 0.0 0 1

Hash Join (cost=12.25..21.54 rows=100 width=50) (actual time=0.016..0.016 rows=0 loops=1)

  • Hash Cond: (vo.c_voucher_id = vr.c_voucher_id)
29. 0.003 0.003 ↑ 239.0 1 1

Seq Scan on c_voucher vo (cost=0.00..7.39 rows=239 width=8) (actual time=0.003..0.003 rows=1 loops=1)

30. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=11.00..11.00 rows=100 width=64) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
31. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on c_voucher_redeem vr (cost=0.00..11.00 rows=100 width=64) (actual time=0.000..0.000 rows=0 loops=1)