explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PzfY

Settings
# exclusive inclusive rows x rows loops node
1. 53.706 3,058.456 ↓ 21,294.0 21,294 1

Sort (cost=34,001.61..34,001.61 rows=1 width=809) (actual time=3,051.804..3,058.456 rows=21,294 loops=1)

  • Sort Key: i.dateinvoiced DESC, (COALESCE(o.created, i.created)) DESC, o.documentno DESC, i.documentno DESC
  • Sort Method: external merge Disk: 4,032kB
2. 23.557 3,004.750 ↓ 21,294.0 21,294 1

Nested Loop Left Join (cost=419.44..34,001.60 rows=1 width=809) (actual time=2.514..3,004.750 rows=21,294 loops=1)

  • Join Filter: (vr.c_invoice_id = i.c_invoice_id)
3. 16.195 2,981.193 ↓ 21,294.0 21,294 1

Nested Loop Left Join (cost=396.16..33,976.33 rows=1 width=578) (actual time=2.507..2,981.193 rows=21,294 loops=1)

  • Join Filter: (i.c_invoice_id = c_voucher.c_invoice_id)
4. 9.090 2,964.998 ↓ 21,294.0 21,294 1

Nested Loop Left Join (cost=385.41..33,963.83 rows=1 width=546) (actual time=2.500..2,964.998 rows=21,294 loops=1)

5. 12.277 2,913.320 ↓ 21,294.0 21,294 1

Nested Loop (cost=385.14..33,963.53 rows=1 width=536) (actual time=2.489..2,913.320 rows=21,294 loops=1)

6. 19.694 2,837.161 ↓ 21,294.0 21,294 1

Nested Loop Left Join (cost=206.00..33,784.37 rows=1 width=516) (actual time=2.158..2,837.161 rows=21,294 loops=1)

  • Join Filter: (payments.c_invoice_id = i.c_invoice_id)
7. 26.226 2,817.467 ↓ 21,294.0 21,294 1

Nested Loop Left Join (cost=133.27..33,710.68 rows=1 width=324) (actual time=2.123..2,817.467 rows=21,294 loops=1)

  • Join Filter: (i.paymentrule = cp.paymentrule)
  • Rows Removed by Join Filter: 64,758
8. 13.932 2,514.419 ↓ 21,294.0 21,294 1

Nested Loop Left Join (cost=133.12..33,709.06 rows=1 width=326) (actual time=2.107..2,514.419 rows=21,294 loops=1)

9. 182.504 2,457.899 ↓ 21,294.0 21,294 1

Nested Loop Left Join (cost=132.85..33,708.77 rows=1 width=312) (actual time=2.100..2,457.899 rows=21,294 loops=1)

  • Filter: ((o.c_charge_id IS NULL) AND (COALESCE(o.ad_client_id, i.ad_client_id) = '1000008'::numeric) AND (COALESCE(o.isactive, i.isactive) = 'Y'::bpchar) AND (COALESCE(o.issotrx, i.issotrx) = 'Y'::bpchar))
  • Rows Removed by Filter: 218,019
10. 149.761 1,557.456 ↓ 18.7 239,313 1

Hash Join (cost=132.43..24,431.99 rows=12,829 width=133) (actual time=1.634..1,557.456 rows=239,313 loops=1)

  • Hash Cond: (i.c_doctype_id = dt.c_doctype_id)
11. 1,406.711 1,406.711 ↓ 1.0 294,309 1

Seq Scan on c_invoice i (cost=0.00..23,527.03 rows=293,965 width=139) (actual time=0.642..1,406.711 rows=294,309 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: 109,870
12. 0.029 0.984 ↑ 1.0 162 1

Hash (cost=130.40..130.40 rows=162 width=6) (actual time=0.984..0.984 rows=162 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
13. 0.955 0.955 ↑ 1.0 162 1

Seq Scan on c_doctype dt (cost=0.00..130.40 rows=162 width=6) (actual time=0.009..0.955 rows=162 loops=1)

  • Filter: (docbasetype = 'ARI'::bpchar)
  • Rows Removed by Filter: 3,550
14. 717.939 717.939 ↑ 1.0 1 239,313

Index Scan using c_order_pkey on c_order o (cost=0.42..0.71 rows=1 width=207) (actual time=0.003..0.003 rows=1 loops=239,313)

  • Index Cond: (i.c_order_id = c_order_id)
15. 42.588 42.588 ↓ 0.0 0 21,294

Index Scan using ad_user_pkey on ad_user ad (cost=0.28..0.30 rows=1 width=23) (actual time=0.002..0.002 rows=0 loops=21,294)

  • Index Cond: (ad_user_id = o.ref_user1_id)
  • Filter: (ad_user_id <> '0'::numeric)
  • Rows Removed by Filter: 1
16. 276.822 276.822 ↑ 1.0 4 21,294

Index Scan using c_paymenttype_uq_name on c_paymenttype cp (cost=0.14..1.57 rows=4 width=13) (actual time=0.012..0.013 rows=4 loops=21,294)

  • Index Cond: (ad_client_id = o.ad_client_id)
17. 0.000 0.000 ↓ 0.0 0 21,294

GroupAggregate (cost=72.73..73.38 rows=14 width=199) (actual time=0.000..0.000 rows=0 loops=21,294)

  • Group Key: payments.c_invoice_id
18. 0.000 0.000 ↓ 0.0 0 21,294

Sort (cost=72.73..72.77 rows=15 width=143) (actual time=0.000..0.000 rows=0 loops=21,294)

  • Sort Key: payments.c_invoice_id
  • Sort Method: quicksort Memory: 25kB
19. 0.000 0.028 ↓ 0.0 0 1

Subquery Scan on payments (cost=0.42..72.44 rows=15 width=143) (actual time=0.028..0.028 rows=0 loops=1)

20. 0.029 0.029 ↓ 0.0 0 1

Index Scan using c_payment_processedon on c_payment p (cost=0.42..72.29 rows=15 width=151) (actual time=0.028..0.029 rows=0 loops=1)

  • Index Cond: (ad_client_id = '1000000'::numeric)
  • Filter: ((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)))
21. 63.570 63.882 ↑ 1.0 1 21,294

Index Scan using c_bpartner_pkey on c_bpartner bp (cost=179.14..179.16 rows=1 width=34) (actual time=0.003..0.003 rows=1 loops=21,294)

  • 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. 0.277 0.312 ↑ 2.0 1 1

Bitmap Heap Scan on c_bpartner (cost=5.45..178.85 rows=2 width=6) (actual time=0.076..0.312 rows=1 loops=1)

  • Recheck Cond: (ad_client_id = '1000008'::numeric)
  • Filter: (ad_orgbp_id <> '0'::numeric)
  • Rows Removed by Filter: 155
  • Heap Blocks: exact=49
24. 0.035 0.035 ↑ 1.0 156 1

Bitmap Index Scan on c_bpartner_value (cost=0.00..5.45 rows=156 width=0) (actual time=0.035..0.035 rows=156 loops=1)

  • Index Cond: (ad_client_id = '1000008'::numeric)
25. 42.588 42.588 ↑ 1.0 1 21,294

Index Scan using ad_user_pkey on ad_user u (cost=0.28..0.29 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=21,294)

  • Index Cond: (COALESCE(o.salesrep_id, o.salesrep_id) = ad_user_id)
26. 0.000 0.000 ↓ 0.0 0 21,294

HashAggregate (cost=10.75..11.38 rows=50 width=48) (actual time=0.000..0.000 rows=0 loops=21,294)

  • Group Key: c_voucher.c_invoice_id
27. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on c_voucher (cost=0.00..10.50 rows=50 width=48) (actual time=0.005..0.005 rows=0 loops=1)

28. 0.000 0.000 ↓ 0.0 0 21,294

HashAggregate (cost=23.27..24.15 rows=50 width=112) (actual time=0.000..0.000 rows=0 loops=21,294)

  • Group Key: vr.c_invoice_id
29. 0.000 0.004 ↓ 0.0 0 1

Hash Join (cost=11.13..22.40 rows=50 width=56) (actual time=0.004..0.004 rows=0 loops=1)

  • Hash Cond: (vr.c_voucher_id = vo.c_voucher_id)
30. 0.004 0.004 ↓ 0.0 0 1

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

31. 0.000 0.000 ↓ 0.0 0

Hash (cost=10.50..10.50 rows=50 width=24) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_voucher vo (cost=0.00..10.50 rows=50 width=24) (never executed)

Planning time : 17.236 ms