explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X7OV

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.074 11,363.167 ↑ 44.9 16 1

Sort (cost=1,320,881.00..1,320,882.79 rows=719 width=220) (actual time=11,363.164..11,363.167 rows=16 loops=1)

  • Sort Key: (sum(b.cost)) DESC
  • Sort Method: quicksort Memory: 33kB
2. 0.242 11,363.093 ↑ 44.9 16 1

Hash Join (cost=1,319,349.25..1,320,846.88 rows=719 width=220) (actual time=11,290.601..11,363.093 rows=16 loops=1)

  • Hash Cond: (b.imb_id = imb.imb_id)
3. 0.083 120.304 ↑ 14.7 45 1

Hash Left Join (cost=4,424.18..5,878.65 rows=660 width=163) (actual time=47.998..120.304 rows=45 loops=1)

  • Hash Cond: (b.imb_id = names.imb_id)
4. 0.256 119.667 ↑ 14.7 45 1

Hash Right Join (cost=4,362.17..5,807.56 rows=660 width=124) (actual time=47.430..119.667 rows=45 loops=1)

  • Hash Cond: (d1.imb_id = b.imb_id)
5. 0.877 115.652 ↓ 4.4 580 1

Nested Loop (cost=3,263.16..4,696.31 rows=133 width=28) (actual time=42.887..115.652 rows=580 loops=1)

6. 27.009 43.381 ↓ 1.0 978 1

HashAggregate (cost=3,262.87..3,272.22 rows=935 width=8) (actual time=42.866..43.381 rows=978 loops=1)

  • Group Key: campaigns.imb_id
7. 16.372 16.372 ↑ 1.0 97,837 1

Seq Scan on campaigns (cost=0.00..2,773.58 rows=97,858 width=8) (actual time=0.011..16.372 rows=97,837 loops=1)

8. 71.394 71.394 ↑ 1.0 1 978

Index Scan using boosts_pkey on campaigns d1 (cost=0.29..1.50 rows=1 width=32) (actual time=0.073..0.073 rows=1 loops=978)

  • Index Cond: ((date = (max(campaigns.date))) AND (imb_id = campaigns.imb_id))
  • Filter: (bid IS NOT NULL)
  • Rows Removed by Filter: 0
9. 0.023 3.759 ↑ 14.7 45 1

Hash (cost=1,090.76..1,090.76 rows=660 width=100) (actual time=3.759..3.759 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 0.690 3.736 ↑ 14.7 45 1

HashAggregate (cost=1,049.51..1,084.16 rows=660 width=100) (actual time=3.691..3.736 rows=45 loops=1)

  • Group Key: b.imb_id
  • Filter: (sum(b.ts_clicks) > 0)
11. 3.046 3.046 ↑ 2.6 441 1

Index Scan using boosts_pkey on campaigns b (cost=0.29..989.77 rows=1,138 width=36) (actual time=0.723..3.046 rows=441 loops=1)

  • Index Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
  • Filter: ((ts_id IS NOT NULL) AND (cost > '0'::double precision))
  • Rows Removed by Filter: 2396
12. 0.277 0.554 ↑ 1.0 1,556 1

Hash (cost=42.56..42.56 rows=1,556 width=43) (actual time=0.554..0.554 rows=1,556 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 119kB
13. 0.277 0.277 ↑ 1.0 1,556 1

Seq Scan on campaign_names_groups names (cost=0.00..42.56 rows=1,556 width=43) (actual time=0.020..0.277 rows=1,556 loops=1)

14. 0.053 11,242.547 ↑ 2.1 105 1

Hash (cost=1,314,922.34..1,314,922.34 rows=218 width=20) (actual time=11,242.547..11,242.547 rows=105 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.014 11,242.494 ↑ 2.1 105 1

Subquery Scan on imb (cost=1,314,917.98..1,314,922.34 rows=218 width=20) (actual time=11,242.458..11,242.494 rows=105 loops=1)

16. 129.829 11,242.480 ↑ 2.1 105 1

HashAggregate (cost=1,314,917.98..1,314,920.16 rows=218 width=20) (actual time=11,242.457..11,242.480 rows=105 loops=1)

  • Group Key: widgets.imb_id
17. 10,320.702 11,112.651 ↑ 6.2 358,177 1

Bitmap Heap Scan on widgets (cost=35,620.08..1,298,236.24 rows=2,224,232 width=12) (actual time=859.468..11,112.651 rows=358,177 loops=1)

  • Recheck Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
  • Rows Removed by Index Recheck: 5506580
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 2481590
  • Heap Blocks: exact=91722 lossy=407067
18. 791.949 791.949 ↓ 1.1 2,848,583 1

Bitmap Index Scan on widgets_date_idx (cost=0.00..35,064.02 rows=2,691,038 width=0) (actual time=791.948..791.949 rows=2,848,583 loops=1)

  • Index Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))