explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KsXK : Optimization for: plan #dnol

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 10,842.658 12,417.503 ↓ 1.0 52,592 1

Hash Left Join (cost=11,145.97..469,882.48 rows=51,341 width=277) (actual time=111.126..12,417.503 rows=52,592 loops=1)

  • Hash Cond: (dc.ad_org_id = o.ad_org_id)
  • Buffers: shared hit=154,973 read=146
2. 92.671 943.576 ↓ 1.0 52,592 1

Hash Left Join (cost=11,132.56..16,335.51 rows=51,341 width=263) (actual time=110.549..943.576 rows=52,592 loops=1)

  • Hash Cond: ((dc.docstatus)::text = (est.value)::text)
  • Buffers: shared hit=5,846 read=146
3. 181.972 850.851 ↓ 1.0 52,592 1

Hash Left Join (cost=11,083.88..15,834.15 rows=51,341 width=251) (actual time=110.482..850.851 rows=52,592 loops=1)

  • Hash Cond: (dc.supervisor_id = ad.ad_user_id)
  • Buffers: shared hit=5,826 read=146
4. 92.887 668.152 ↓ 1.0 52,592 1

Hash Left Join (cost=11,010.67..15,055.51 rows=51,341 width=242) (actual time=109.744..668.152 rows=52,592 loops=1)

  • Hash Cond: (dc.c_period_id = pp.c_period_id)
  • Buffers: shared hit=5,773 read=146
5. 63.865 575.029 ↓ 1.0 52,592 1

Hash Left Join (cost=10,996.57..14,335.65 rows=51,341 width=242) (actual time=109.501..575.029 rows=52,592 loops=1)

  • Hash Cond: ((dc.categorytype)::text = (cat.value)::text)
  • Buffers: shared hit=5,767 read=146
6. 66.553 511.133 ↓ 1.0 52,592 1

Hash Left Join (cost=10,977.42..13,866.64 rows=51,341 width=233) (actual time=109.462..511.133 rows=52,592 loops=1)

  • Hash Cond: (dc.c_projectofb_id = ofb.c_projectofb_id)
  • Buffers: shared hit=5,760 read=146
7. 152.972 444.497 ↓ 1.0 52,592 1

Hash Left Join (cost=10,968.64..13,151.92 rows=51,341 width=218) (actual time=109.364..444.497 rows=52,592 loops=1)

  • Hash Cond: (dc.c_bpartner_id = cp.c_bpartner_id)
  • Buffers: shared hit=5,755 read=146
8. 101.752 285.896 ↓ 1.0 52,592 1

Hash Left Join (cost=10,533.55..11,789.10 rows=51,341 width=196) (actual time=103.711..285.896 rows=52,592 loops=1)

  • Hash Cond: (dc.a_asset_id = a.a_asset_id)
  • Buffers: shared hit=5,482 read=146
9. 80.464 182.848 ↓ 1.0 52,592 1

Hash Right Join (cost=10,294.40..10,715.65 rows=51,341 width=196) (actual time=102.386..182.848 rows=52,592 loops=1)

  • Hash Cond: ((cat2.value)::text = (dc."interval")::text)
  • Buffers: shared hit=5,302 read=146
10. 0.065 0.101 ↑ 1.0 37 1

Bitmap Heap Scan on ad_ref_list cat2 (cost=4.56..52.90 rows=37 width=14) (actual time=0.043..0.101 rows=37 loops=1)

  • Recheck Cond: (ad_reference_id = 1,000,096::numeric)
  • Heap Blocks: exact=4
  • Buffers: shared hit=6
11. 0.036 0.036 ↓ 1.0 38 1

Bitmap Index Scan on ad_ref_list_value (cost=0.00..4.55 rows=37 width=0) (actual time=0.036..0.036 rows=38 loops=1)

  • Index Cond: (ad_reference_id = 1,000,096::numeric)
  • Buffers: shared hit=2
12. 45.722 102.283 ↓ 1.0 52,592 1

Hash (cost=9,648.08..9,648.08 rows=51,341 width=187) (actual time=102.283..102.283 rows=52,592 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 11,185kB
  • Buffers: shared hit=5,296 read=146
13. 47.522 56.561 ↓ 1.0 52,592 1

Bitmap Heap Scan on tp_driverscontrol dc (cost=966.31..9,648.08 rows=51,341 width=187) (actual time=10.382..56.561 rows=52,592 loops=1)

  • Recheck Cond: ((typematrix)::text = 'DE'::text)
  • Heap Blocks: exact=5,296
  • Buffers: shared hit=5,296 read=146
14. 9.039 9.039 ↓ 1.0 52,592 1

Bitmap Index Scan on tp_driverscontrol_typematrix_idx (cost=0.00..953.48 rows=51,341 width=0) (actual time=9.039..9.039 rows=52,592 loops=1)

  • Index Cond: ((typematrix)::text = 'DE'::text)
  • Buffers: shared read=146
15. 0.589 1.296 ↑ 1.6 1,655 1

Hash (cost=206.29..206.29 rows=2,629 width=14) (actual time=1.296..1.296 rows=1,655 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 76kB
  • Buffers: shared hit=180
16. 0.707 0.707 ↑ 1.6 1,655 1

Seq Scan on a_asset a (cost=0.00..206.29 rows=2,629 width=14) (actual time=0.008..0.707 rows=1,655 loops=1)

  • Buffers: shared hit=180
17. 2.964 5.629 ↑ 1.0 7,187 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 479kB
  • Buffers: shared hit=273
18. 2.665 2.665 ↑ 1.0 7,187 1

Seq Scan on c_bpartner cp (cost=0.00..345.04 rows=7,204 width=35) (actual time=0.004..2.665 rows=7,187 loops=1)

  • Buffers: shared hit=273
19. 0.054 0.083 ↑ 1.8 91 1

Hash (cost=6.68..6.68 rows=168 width=29) (actual time=0.083..0.083 rows=91 loops=1)

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

Seq Scan on c_projectofb ofb (cost=0.00..6.68 rows=168 width=29) (actual time=0.006..0.029 rows=91 loops=1)

  • Buffers: shared hit=5
21. 0.004 0.031 ↓ 1.8 9 1

Hash (cost=19.09..19.09 rows=5 width=14) (actual time=0.031..0.031 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=7
22. 0.027 0.027 ↓ 1.8 9 1

Index Scan using ad_ref_list_value on ad_ref_list cat (cost=0.28..19.09 rows=5 width=14) (actual time=0.017..0.027 rows=9 loops=1)

  • Index Cond: (ad_reference_id = 1,000,090::numeric)
  • Buffers: shared hit=7
23. 0.141 0.236 ↑ 1.3 276 1

Hash (cost=9.60..9.60 rows=360 width=13) (actual time=0.236..0.236 rows=276 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=6
24. 0.095 0.095 ↑ 1.3 276 1

Seq Scan on c_period pp (cost=0.00..9.60 rows=360 width=13) (actual time=0.005..0.095 rows=276 loops=1)

  • Buffers: shared hit=6
25. 0.348 0.727 ↑ 1.0 898 1

Hash (cost=61.98..61.98 rows=898 width=22) (actual time=0.727..0.727 rows=898 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 49kB
  • Buffers: shared hit=53
26. 0.379 0.379 ↑ 1.0 898 1

Seq Scan on ad_user ad (cost=0.00..61.98 rows=898 width=22) (actual time=0.006..0.379 rows=898 loops=1)

  • Buffers: shared hit=53
27. 0.003 0.054 ↑ 1.0 5 1

Hash (cost=48.62..48.62 rows=5 width=14) (actual time=0.054..0.054 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=20
28. 0.015 0.051 ↑ 1.0 5 1

Nested Loop Left Join (cost=0.56..48.62 rows=5 width=14) (actual time=0.029..0.051 rows=5 loops=1)

  • Buffers: shared hit=20
29. 0.011 0.011 ↑ 1.0 5 1

Index Scan using ad_ref_list_value on ad_ref_list est (cost=0.28..19.09 rows=5 width=20) (actual time=0.008..0.011 rows=5 loops=1)

  • Index Cond: (ad_reference_id = 1,000,091::numeric)
  • Buffers: shared hit=4
30. 0.025 0.025 ↑ 1.0 1 5

Index Only Scan using ad_ref_list_trl_pkey on ad_ref_list_trl estt (cost=0.28..5.90 rows=1 width=6) (actual time=0.005..0.005 rows=1 loops=5)

  • Index Cond: (ad_ref_list_id = est.ad_ref_list_id)
  • Heap Fetches: 5
  • Buffers: shared hit=16
31. 0.077 0.165 ↓ 1.1 220 1

Hash (cost=10.96..10.96 rows=196 width=23) (actual time=0.165..0.165 rows=220 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=9
32. 0.088 0.088 ↓ 1.1 220 1

Seq Scan on ad_org o (cost=0.00..10.96 rows=196 width=23) (actual time=0.006..0.088 rows=220 loops=1)

  • Buffers: shared hit=9
33.          

SubPlan (for Hash Left Join)

34. 631.104 631.104 ↑ 1.0 1 52,592

Index Scan using ad_user_pkey on ad_user u (cost=0.28..8.29 rows=1 width=17) (actual time=0.010..0.012 rows=1 loops=52,592)

  • Index Cond: (ad_user_id = ofb.ad_user_id)
  • Buffers: shared hit=149,118
Planning time : 7.748 ms
Execution time : 12,430.316 ms