explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dnol

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 11,919.355 14,121.508 ↓ 1.1 52,592 1

Hash Left Join (cost=12,295.24..443,344.81 rows=48,242 width=277) (actual time=256.145..14,121.508 rows=52,592 loops=1)

  • Hash Cond: (dc.ad_org_id = o.ad_org_id)
  • Buffers: shared hit=157,717
2. 405.631 1,465.702 ↓ 1.1 52,592 1

Hash Left Join (cost=12,281.83..17,173.64 rows=48,242 width=263) (actual time=255.640..1,465.702 rows=52,592 loops=1)

  • Hash Cond: ((dc.docstatus)::text = (est.value)::text)
  • Buffers: shared hit=8,590
3. 86.341 1,059.991 ↓ 1.1 52,592 1

Hash Left Join (cost=12,233.14..16,699.59 rows=48,242 width=251) (actual time=255.546..1,059.991 rows=52,592 loops=1)

  • Hash Cond: (dc.supervisor_id = ad.ad_user_id)
  • Buffers: shared hit=8,570
4. 67.446 972.936 ↓ 1.1 52,592 1

Hash Left Join (cost=12,159.94..15,963.54 rows=48,242 width=242) (actual time=254.779..972.936 rows=52,592 loops=1)

  • Hash Cond: (dc.c_period_id = pp.c_period_id)
  • Buffers: shared hit=8,517
5. 126.165 905.316 ↓ 1.1 52,592 1

Hash Left Join (cost=12,145.84..15,286.27 rows=48,242 width=242) (actual time=254.597..905.316 rows=52,592 loops=1)

  • Hash Cond: ((dc.categorytype)::text = (cat.value)::text)
  • Buffers: shared hit=8,511
6. 78.340 779.125 ↓ 1.1 52,592 1

Hash Left Join (cost=12,126.69..14,844.43 rows=48,242 width=233) (actual time=254.564..779.125 rows=52,592 loops=1)

  • Hash Cond: (dc.c_projectofb_id = ofb.c_projectofb_id)
  • Buffers: shared hit=8,504
7. 204.719 700.706 ↓ 1.1 52,592 1

Hash Left Join (cost=12,117.91..14,172.32 rows=48,242 width=218) (actual time=254.476..700.706 rows=52,592 loops=1)

  • Hash Cond: (dc.c_bpartner_id = cp.c_bpartner_id)
  • Buffers: shared hit=8,499
8. 117.808 490.238 ↓ 1.1 52,592 1

Hash Left Join (cost=11,682.82..12,865.50 rows=48,242 width=196) (actual time=248.713..490.238 rows=52,592 loops=1)

  • Hash Cond: (dc.a_asset_id = a.a_asset_id)
  • Buffers: shared hit=8,226
9. 123.767 371.025 ↓ 1.1 52,592 1

Hash Right Join (cost=11,443.66..11,842.41 rows=48,242 width=196) (actual time=247.291..371.025 rows=52,592 loops=1)

  • Hash Cond: ((cat2.value)::text = (dc."interval")::text)
  • Buffers: shared hit=8,046
10. 0.063 0.093 ↑ 1.0 37 1

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

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

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

  • Index Cond: (ad_reference_id = 1,000,096::numeric)
  • Buffers: shared hit=2
12. 87.811 247.165 ↓ 1.1 52,592 1

Hash (cost=10,836.08..10,836.08 rows=48,242 width=187) (actual time=247.165..247.165 rows=52,592 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 11,185kB
  • Buffers: shared hit=8,040
13. 159.354 159.354 ↓ 1.1 52,592 1

Seq Scan on tp_driverscontrol dc (cost=0.00..10,836.08 rows=48,242 width=187) (actual time=0.012..159.354 rows=52,592 loops=1)

  • Filter: ((typematrix)::text = 'DE'::text)
  • Rows Removed by Filter: 185,463
  • Buffers: shared hit=8,040
14. 0.700 1.405 ↑ 1.6 1,655 1

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

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

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

  • Buffers: shared hit=180
16. 3.102 5.749 ↑ 1.0 7,187 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 479kB
  • Buffers: shared hit=273
17. 2.647 2.647 ↑ 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.647 rows=7,187 loops=1)

  • Buffers: shared hit=273
18. 0.030 0.079 ↑ 1.8 91 1

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

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

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

  • Buffers: shared hit=5
20. 0.005 0.026 ↓ 1.8 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=7
21. 0.021 0.021 ↓ 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.013..0.021 rows=9 loops=1)

  • Index Cond: (ad_reference_id = 1,000,090::numeric)
  • Buffers: shared hit=7
22. 0.087 0.174 ↑ 1.3 276 1

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

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

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

  • Buffers: shared hit=6
24. 0.349 0.714 ↑ 1.0 898 1

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

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

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

  • Buffers: shared hit=53
26. 0.004 0.080 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=20
27. 0.013 0.076 ↑ 1.0 5 1

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

  • Buffers: shared hit=20
28. 0.013 0.013 ↑ 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.013 rows=5 loops=1)

  • Index Cond: (ad_reference_id = 1,000,091::numeric)
  • Buffers: shared hit=4
29. 0.050 0.050 ↑ 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.010..0.010 rows=1 loops=5)

  • Index Cond: (ad_ref_list_id = est.ad_ref_list_id)
  • Heap Fetches: 5
  • Buffers: shared hit=16
30. 0.080 0.163 ↓ 1.1 220 1

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

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

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

  • Buffers: shared hit=9
32.          

SubPlan (for Hash Left Join)

33. 736.288 736.288 ↑ 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.013..0.014 rows=1 loops=52,592)

  • Index Cond: (ad_user_id = ofb.ad_user_id)
  • Buffers: shared hit=149,118
Planning time : 9.750 ms
Execution time : 14,141.789 ms