TNrF : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #jjT8; plan #R0k; plan #RqOF; plan #yjc; plan #fWeZ; plan #X2Wd; plan #DsHq; plan #PaBF | explain.depesz.com
Result: TNrF : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #jjT8; plan #R0k; plan #RqOF; plan #yjc; plan #fWeZ; plan #X2Wd; plan #DsHq; plan #PaBF
1. 0.057 9,518.141 ↑ 61.0 26 1
Sort (cost=69,477.96..69,481.92 rows=1,585 width=220) (actual time=9,518.137..9,518.141 rows=26 loops=1)
2. 0.171 9,518.084 ↑ 61.0 26 1
Hash Join (cost=67,461.12..69,393.71 rows=1,585 width=220) (actual time=9,451.416..9,518.084 rows=26 loops=1)
3. 0.054 124.194 ↑ 10.8 44 1
Hash Left Join (cost=4,454.64..6,294.92 rows=473 width=163) (actual time=56.958..124.194 rows=44 loops=1)
4. 0.206 121.730 ↑ 10.8 44 1
Hash Right Join (cost=4,367.69..6,201.47 rows=473 width=124) (actual time=54.534..121.730 rows=44 loops=1)
5. 0.913 110.380 ↓ 4.4 580 1
Nested Loop (cost=3,567.46..5,392.50 rows=132 width=28) (actual time=42.544..110.380 rows=580 loops=1)
6. 24.850 42.963 ↓ 1.0 978 1
HashAggregate (cost=3,567.05..3,576.45 rows=940 width=8) (actual time=42.516..42.963 rows=978 loops=1)
7. 18.113 18.113 ↑ 1.0 97,803 1
Seq Scan on campaigns (cost=0.00..3,078.03 rows=97,803 width=8) (actual time=0.011..18.113 rows=97,803 loops=1)
8. 66.504 66.504 ↑ 1.0 1 978
Index Scan using boosts_pkey on campaigns d1 (cost=0.42..1.91 rows=1 width=32) (actual time=0.068..0.068 rows=1 loops=978)
9. 0.017 11.144 ↑ 10.8 44 1
Hash (cost=794.32..794.32 rows=473 width=100) (actual time=11.144..11.144 rows=44 loops=1)
10. 0.235 11.127 ↑ 10.8 44 1
HashAggregate (cost=764.76..789.59 rows=473 width=100) (actual time=11.074..11.127 rows=44 loops=1)
11. 10.892 10.892 ↑ 2.7 246 1
Index Scan using boosts_pkey on campaigns b (cost=0.42..730.37 rows=655 width=36) (actual time=4.457..10.892 rows=246 loops=1)
12. 0.300 2.410 ↑ 1.0 1,553 1
Hash (cost=67.53..67.53 rows=1,553 width=43) (actual time=2.410..2.410 rows=1,553 loops=1)
13. 2.110 2.110 ↑ 1.0 1,553 1
Seq Scan on campaign_names_groups names (cost=0.00..67.53 rows=1,553 width=43) (actual time=0.382..2.110 rows=1,553 loops=1)
14. 0.015 9,393.719 ↑ 25.8 26 1
Hash (cost=62,998.11..62,998.11 rows=670 width=20) (actual time=9,393.719..9,393.719 rows=26 loops=1)
15. 0.005 9,393.704 ↑ 25.8 26 1
Subquery Scan on imb (cost=62,984.71..62,998.11 rows=670 width=20) (actual time=9,393.692..9,393.704 rows=26 loops=1)
16. 455.619 9,393.699 ↑ 25.8 26 1
HashAggregate (cost=62,984.71..62,991.41 rows=670 width=20) (actual time=9,393.692..9,393.699 rows=26 loops=1)
17. 7,297.064 8,938.080 ↓ 18.9 1,477,103 1
Bitmap Heap Scan on widgets (cost=40,452.12..62,399.01 rows=78,094 width=12) (actual time=1,659.385..8,938.080 rows=1,477,103 loops=1)
18. 69.792 1,641.016 ↓ 0.0 0 1
BitmapAnd (cost=40,452.12..40,452.12 rows=19,981 width=0) (actual time=1,641.016..1,641.016 rows=0 loops=1)
19. 964.052 964.052 ↓ 4.0 3,686,960 1
Bitmap Index Scan on widgets_source_id_idx (cost=0.00..9,707.48 rows=922,325 width=0) (actual time=964.052..964.052 rows=3,686,960 loops=1)
20. 607.172 607.172 ↑ 1.1 1,647,809 1
Bitmap Index Scan on widgets_pkey (cost=0.00..30,705.35 rows=1,747,068 width=0) (actual time=607.172..607.172 rows=1,647,809 loops=1)
: 9,518.322 ms
Sort  (cost=69477.96..69481.92 rows=1585 width=220) (actual time=9518.137..9518.141 rows=26 loops=1)
->  Hash Join  (cost=67461.12..69393.71 rows=1585 width=220) (actual time=9451.416..9518.084 rows=26 loops=1)
->  Hash Left Join  (cost=4454.64..6294.92 rows=473 width=163) (actual time=56.958..124.194 rows=44 loops=1)
->  Hash Right Join  (cost=4367.69..6201.47 rows=473 width=124) (actual time=54.534..121.730 rows=44 loops=1)
->  Nested Loop  (cost=3567.46..5392.50 rows=132 width=28) (actual time=42.544..110.380 rows=580 loops=1)
->  HashAggregate  (cost=3567.05..3576.45 rows=940 width=8) (actual time=42.516..42.963 rows=978 loops=1)
->  Seq Scan on campaigns  (cost=0.00..3078.03 rows=97803 width=8) (actual time=0.011..18.113 rows=97803 loops=1)
->  Index Scan using boosts_pkey on campaigns d1  (cost=0.42..1.91 rows=1 width=32) (actual time=0.068..0.068 rows=1 loops=978)
->  Hash  (cost=794.32..794.32 rows=473 width=100) (actual time=11.144..11.144 rows=44 loops=1)
->  HashAggregate  (cost=764.76..789.59 rows=473 width=100) (actual time=11.074..11.127 rows=44 loops=1)
->  Index Scan using boosts_pkey on campaigns b  (cost=0.42..730.37 rows=655 width=36) (actual time=4.457..10.892 rows=246 loops=1)
->  Hash  (cost=67.53..67.53 rows=1553 width=43) (actual time=2.410..2.410 rows=1553 loops=1)
->  Seq Scan on campaign_names_groups names  (cost=0.00..67.53 rows=1553 width=43) (actual time=0.382..2.110 rows=1553 loops=1)
->  Hash  (cost=62998.11..62998.11 rows=670 width=20) (actual time=9393.719..9393.719 rows=26 loops=1)
->  Subquery Scan on imb  (cost=62984.71..62998.11 rows=670 width=20) (actual time=9393.692..9393.704 rows=26 loops=1)
->  HashAggregate  (cost=62984.71..62991.41 rows=670 width=20) (actual time=9393.692..9393.699 rows=26 loops=1)
->  Bitmap Heap Scan on widgets  (cost=40452.12..62399.01 rows=78094 width=12) (actual time=1659.385..8938.080 rows=1477103 loops=1)
->  BitmapAnd  (cost=40452.12..40452.12 rows=19981 width=0) (actual time=1641.016..1641.016 rows=0 loops=1)
->  Bitmap Index Scan on widgets_source_id_idx  (cost=0.00..9707.48 rows=922325 width=0) (actual time=964.052..964.052 rows=3686960 loops=1)
->  Bitmap Index Scan on widgets_pkey  (cost=0.00..30705.35 rows=1747068 width=0) (actual time=607.172..607.172 rows=1647809 loops=1)
Execution time: 9518.322 ms
Bitmap Heap Scan 1 7,297.064 ms 76.7 %
Bitmap Index Scan 2 1,571.224 ms 16.5 %
BitmapAnd 1 69.792 ms 0.7 %
Hash 3 0.332 ms 0.0 %
Hash Join 1 0.171 ms 0.0 %
Hash Left Join 1 0.054 ms 0.0 %
HashAggregate 3 480.704 ms 5.1 %
Index Scan 2 77.396 ms 0.8 %
Nested Loop 1 0.913 ms 0.0 %
Seq Scan 2 20.223 ms 0.2 %
campaign_names_groups 1 2.110 ms 0.0 %
Seq Scan 1 2.110 ms 100.0 %
campaigns 3 95.509 ms 1.0 %
Index Scan 2 77.396 ms 81.0 %
Seq Scan 1 18.113 ms 19.0 %
widgets 1 7,297.064 ms 76.7 %
Bitmap Heap Scan 1 7,297.064 ms 100.0 %