explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SYsT

Settings
# exclusive inclusive rows x rows loops node
1. 32,826.407 46,141.135 ↓ 13.4 302,102 1

Hash Left Join (cost=22,515.12..407,443.99 rows=22,486 width=120) (actual time=3,532.383..46,141.135 rows=302,102 loops=1)

  • Hash Cond: (prl.c_paymentrequest_id = pr.c_paymentrequest_id)
  • Buffers: shared hit=1,826,931 read=640
2. 968.381 4,502.336 ↓ 13.4 302,102 1

Hash Right Join (cost=20,107.00..25,500.30 rows=22,486 width=111) (actual time=3,480.507..4,502.336 rows=302,102 loops=1)

  • Hash Cond: (prl.dm_document_id = d.dm_document_id)
  • Buffers: shared hit=12,769 read=640
3. 53.689 53.689 ↑ 1.0 120,252 1

Seq Scan on c_paymentrequestline prl (cost=0.00..4,194.83 rows=122,183 width=13) (actual time=0.005..53.689 rows=120,252 loops=1)

  • Buffers: shared hit=2,333 read=640
4. 344.739 3,480.266 ↓ 13.4 301,828 1

Hash (cost=19,825.92..19,825.92 rows=22,486 width=112) (actual time=3,480.266..3,480.266 rows=301,828 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 41,242kB
  • Buffers: shared hit=10,436
5. 204.499 3,135.527 ↓ 13.4 301,828 1

Hash Left Join (cost=7,642.66..19,825.92 rows=22,486 width=112) (actual time=221.090..3,135.527 rows=301,828 loops=1)

  • Hash Cond: (rl.tp_refundamt_id = ra.tp_refundamt_id)
  • Buffers: shared hit=10,436
6. 405.274 2,930.588 ↓ 13.4 301,828 1

Hash Join (cost=7,625.03..19,685.22 rows=22,486 width=100) (actual time=220.621..2,930.588 rows=301,828 loops=1)

  • Hash Cond: (r.c_bpartner_id = bp.c_bpartner_id)
  • Buffers: shared hit=10,428
7. 1,234.359 2,515.944 ↓ 13.4 301,828 1

Hash Join (cost=7,189.94..18,772.30 rows=22,486 width=65) (actual time=211.237..2,515.944 rows=301,828 loops=1)

  • Hash Cond: (rl.tp_refund_id = r.tp_refund_id)
  • Buffers: shared hit=10,155
8. 1,070.418 1,070.418 ↓ 3.1 301,933 1

Seq Scan on tp_refundline rl (cost=0.00..10,877.02 rows=96,096 width=27) (actual time=0.028..1,070.418 rows=301,933 loops=1)

  • Filter: ((date_part('Years'::text, datetrx))::numeric >= 2,018::numeric)
  • Rows Removed by Filter: 7,108
  • Buffers: shared hit=5,832
9. 50.636 211.167 ↓ 4.3 79,424 1

Hash (cost=6,958.78..6,958.78 rows=18,493 width=52) (actual time=211.167..211.167 rows=79,424 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 6,447kB
  • Buffers: shared hit=4,323
10. 103.129 160.531 ↓ 4.3 79,424 1

Hash Join (cost=3,546.33..6,958.78 rows=18,493 width=52) (actual time=43.460..160.531 rows=79,424 loops=1)

  • Hash Cond: (r.tp_refundheader_id = rh.tp_refundheader_id)
  • Buffers: shared hit=4,323
11. 14.043 14.043 ↓ 1.0 79,530 1

Seq Scan on tp_refund r (cost=0.00..2,832.33 rows=79,033 width=21) (actual time=0.004..14.043 rows=79,530 loops=1)

  • Buffers: shared hit=2,042
12. 4.533 43.359 ↓ 4.2 8,320 1

Hash (cost=3,521.76..3,521.76 rows=1,966 width=45) (actual time=43.359..43.359 rows=8,320 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 623kB
  • Buffers: shared hit=2,281
13. 14.535 38.826 ↓ 4.2 8,320 1

Hash Right Join (cost=443.17..3,521.76 rows=1,966 width=45) (actual time=14.176..38.826 rows=8,320 loops=1)

  • Hash Cond: (d.tp_refundheader_id = rh.tp_refundheader_id)
  • Buffers: shared hit=2,281
14. 10.179 10.179 ↑ 1.0 68,544 1

Seq Scan on dm_document d (cost=0.00..2,726.22 rows=70,022 width=14) (actual time=0.003..10.179 rows=68,544 loops=1)

  • Buffers: shared hit=2,026
15. 4.136 14.112 ↓ 4.2 8,320 1

Hash (cost=418.59..418.59 rows=1,966 width=38) (actual time=14.112..14.112 rows=8,320 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 567kB
  • Buffers: shared hit=255
16. 6.333 9.976 ↓ 4.2 8,320 1

Hash Join (cost=21.51..418.59 rows=1,966 width=38) (actual time=0.272..9.976 rows=8,320 loops=1)

  • Hash Cond: (rh.ad_org_id = o.ad_org_id)
  • Buffers: shared hit=255
17. 3.383 3.383 ↑ 1.0 8,320 1

Seq Scan on tp_refundheader rh (cost=0.00..346.01 rows=8,376 width=27) (actual time=0.007..3.383 rows=8,320 loops=1)

  • Filter: ((docstatus)::text = 'CO'::text)
  • Rows Removed by Filter: 10
  • Buffers: shared hit=241
18. 0.025 0.260 ↓ 1.6 73 1

Hash (cost=20.94..20.94 rows=46 width=24) (actual time=0.260..0.260 rows=73 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 5kB
  • Buffers: shared hit=14
19. 0.097 0.235 ↓ 1.6 73 1

Hash Join (cost=8.78..20.94 rows=46 width=24) (actual time=0.115..0.235 rows=73 loops=1)

  • Hash Cond: (o.c_projectofb_id = f.c_projectofb_id)
  • Buffers: shared hit=14
20. 0.041 0.041 ↓ 1.1 220 1

Seq Scan on ad_org o (cost=0.00..10.96 rows=196 width=13) (actual time=0.004..0.041 rows=220 loops=1)

  • Buffers: shared hit=9
21. 0.035 0.097 ↑ 1.8 91 1

Hash (cost=6.68..6.68 rows=168 width=25) (actual time=0.097..0.097 rows=91 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 6kB
  • Buffers: shared hit=5
22. 0.062 0.062 ↑ 1.8 91 1

Seq Scan on c_projectofb f (cost=0.00..6.68 rows=168 width=25) (actual time=0.006..0.062 rows=91 loops=1)

  • Buffers: shared hit=5
23. 3.407 9.370 ↑ 1.0 7,187 1

Hash (cost=345.04..345.04 rows=7,204 width=48) (actual time=9.370..9.370 rows=7,187 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 573kB
  • Buffers: shared hit=273
24. 5.963 5.963 ↑ 1.0 7,187 1

Seq Scan on c_bpartner bp (cost=0.00..345.04 rows=7,204 width=48) (actual time=0.009..5.963 rows=7,187 loops=1)

  • Buffers: shared hit=273
25. 0.234 0.440 ↓ 1.0 430 1

Hash (cost=12.28..12.28 rows=428 width=26) (actual time=0.440..0.440 rows=430 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
  • Buffers: shared hit=8
26. 0.206 0.206 ↓ 1.0 430 1

Seq Scan on tp_refundamt ra (cost=0.00..12.28 rows=428 width=26) (actual time=0.010..0.206 rows=430 loops=1)

  • Buffers: shared hit=8
27. 4.869 51.434 ↑ 1.0 10,273 1

Hash (cost=2,279.16..2,279.16 rows=10,317 width=22) (actual time=51.434..51.434 rows=10,273 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 451kB
  • Buffers: shared hit=1,550
28. 12.684 46.565 ↑ 1.0 10,273 1

Hash Right Join (cost=709.13..2,279.16 rows=10,317 width=22) (actual time=11.583..46.565 rows=10,273 loops=1)

  • Hash Cond: (p.c_payment_id = pr.c_payment_id)
  • Buffers: shared hit=1,550
29. 22.346 22.346 ↓ 1.0 20,755 1

Seq Scan on c_payment p (cost=0.00..1,355.51 rows=20,454 width=22) (actual time=0.013..22.346 rows=20,755 loops=1)

  • Filter: (docstatus = 'CO'::bpchar)
  • Rows Removed by Filter: 2,111
  • Buffers: shared hit=1,073
30. 4.277 11.535 ↑ 1.0 10,273 1

Hash (cost=580.17..580.17 rows=10,317 width=14) (actual time=11.535..11.535 rows=10,273 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 418kB
  • Buffers: shared hit=477
31. 7.258 7.258 ↑ 1.0 10,273 1

Seq Scan on c_paymentrequest pr (cost=0.00..580.17 rows=10,317 width=14) (actual time=0.006..7.258 rows=10,273 loops=1)

  • Buffers: shared hit=477
32.          

SubPlan (for Hash Left Join)

33. 3,927.326 3,927.326 ↑ 1.0 1 302,102

Index Scan using i_ad_ref_list_value_reference on ad_ref_list rl_1 (cost=0.28..8.30 rows=1 width=12) (actual time=0.012..0.013 rows=1 loops=302,102)

  • Index Cond: (((value)::text = (rh.type)::text) AND (ad_reference_id = 1,000,092::numeric))
  • Buffers: shared hit=906,306
34. 4,833.632 4,833.632 ↑ 1.0 1 302,102

Index Scan using i_ad_ref_list_value_reference on ad_ref_list rl_1_1 (cost=0.28..8.30 rows=1 width=12) (actual time=0.015..0.016 rows=1 loops=302,102)

  • Index Cond: (((value)::text = (f.location)::text) AND (ad_reference_id = 1,000,197::numeric))
  • Buffers: shared hit=906,306
Planning time : 4.753 ms
Execution time : 46,219.781 ms