explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vm5S : Optimization for: Optimization for: Optimization for: plan #X7OV; plan #4qTb; plan #oZWp

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.122 10,439.632 ↑ 13.5 12 1

Merge Left Join (cost=1,311,524.05..1,311,535.96 rows=162 width=148) (actual time=10,439.550..10,439.632 rows=12 loops=1)

  • Merge Cond: (b.date = n.date)
2. 0.028 10,432.641 ↑ 13.5 12 1

Merge Join (cost=1,311,522.75..1,311,525.99 rows=162 width=68) (actual time=10,432.625..10,432.641 rows=12 loops=1)

  • Merge Cond: (b.date = wid.date)
3. 0.022 6.135 ↑ 13.6 12 1

Sort (cost=1,679.56..1,679.96 rows=163 width=60) (actual time=6.131..6.135 rows=12 loops=1)

  • Sort Key: b.date DESC
  • Sort Method: quicksort Memory: 26kB
4. 0.002 6.113 ↑ 13.6 12 1

Subquery Scan on b (cost=0.29..1,673.57 rows=163 width=60) (actual time=2.212..6.113 rows=12 loops=1)

5. 0.804 6.111 ↑ 13.6 12 1

GroupAggregate (cost=0.29..1,671.94 rows=163 width=64) (actual time=2.212..6.111 rows=12 loops=1)

  • Group Key: campaigns.date
6. 5.307 5.307 ↑ 1.0 2,700 1

Index Scan using boosts_pkey on campaigns (cost=0.29..1,592.14 rows=2,783 width=32) (actual time=1.970..5.307 rows=2,700 loops=1)

  • Index Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 363
7. 0.035 10,426.478 ↑ 13.5 12 1

Sort (cost=1,309,843.19..1,309,843.60 rows=162 width=12) (actual time=10,426.475..10,426.478 rows=12 loops=1)

  • Sort Key: wid.date DESC
  • Sort Method: quicksort Memory: 25kB
8. 0.002 10,426.443 ↑ 13.5 12 1

Subquery Scan on wid (cost=1,309,834.00..1,309,837.24 rows=162 width=12) (actual time=10,426.437..10,426.443 rows=12 loops=1)

9. 123.622 10,426.441 ↑ 13.5 12 1

HashAggregate (cost=1,309,834.00..1,309,835.62 rows=162 width=20) (actual time=10,426.435..10,426.441 rows=12 loops=1)

  • Group Key: widgets.date
10. 8,907.903 10,302.819 ↑ 6.1 369,057 1

Bitmap Heap Scan on widgets (cost=35,842.36..1,298,664.26 rows=2,233,949 width=8) (actual time=1,530.712..10,302.819 rows=369,057 loops=1)

  • Recheck Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
  • Rows Removed by Index Recheck: 5374071
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 2493532
  • Heap Blocks: exact=94674 lossy=407089
11. 1,394.916 1,394.916 ↓ 1.1 3,022,896 1

Bitmap Index Scan on widgets_date_idx (cost=0.00..35,283.88 rows=2,702,794 width=0) (actual time=1,394.916..1,394.916 rows=3,022,896 loops=1)

  • Index Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
12. 0.032 6.869 ↑ 11.0 1 1

Sort (cost=1.30..1.33 rows=11 width=36) (actual time=6.868..6.869 rows=1 loops=1)

  • Sort Key: n.date DESC
  • Sort Method: quicksort Memory: 25kB
13. 6.837 6.837 ↑ 1.0 11 1

Seq Scan on notes n (cost=0.00..1.11 rows=11 width=36) (actual time=6.834..6.837 rows=11 loops=1)

Planning time : 1.957 ms
Execution time : 10,439.769 ms