explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.156 11,024.675 ↑ 13.6 12 1

Merge Join (cost=1,324,744.61..1,324,756.59 rows=163 width=148) (actual time=11,024.570..11,024.675 rows=12 loops=1)

  • Merge Cond: (b.date = wid.date)
2. 0.006 4.486 ↑ 13.6 12 1

Merge Left Join (cost=1,680.86..1,681.84 rows=163 width=92) (actual time=4.480..4.486 rows=12 loops=1)

  • Merge Cond: (b.date = n.date)
3. 0.019 4.457 ↑ 13.6 12 1

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

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

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

5. 0.938 4.435 ↑ 13.6 12 1

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

  • Group Key: campaigns.date
6. 3.497 3.497 ↑ 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=0.506..3.497 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.011 0.023 ↑ 11.0 1 1

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

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

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

9. 0.024 11,020.033 ↑ 13.6 12 1

Sort (cost=1,323,063.75..1,323,064.16 rows=163 width=12) (actual time=11,020.030..11,020.033 rows=12 loops=1)

  • Sort Key: wid.date DESC
  • Sort Method: quicksort Memory: 25kB
10. 0.002 11,020.009 ↑ 13.6 12 1

Subquery Scan on wid (cost=1,323,054.50..1,323,057.76 rows=163 width=12) (actual time=11,020.007..11,020.009 rows=12 loops=1)

11. 136.016 11,020.007 ↑ 13.6 12 1

HashAggregate (cost=1,323,054.50..1,323,056.13 rows=163 width=20) (actual time=11,020.006..11,020.007 rows=12 loops=1)

  • Group Key: widgets.date
12. 9,404.519 10,883.991 ↑ 6.4 391,432 1

Bitmap Heap Scan on widgets (cost=40,898.92..1,310,456.27 rows=2,519,647 width=8) (actual time=1,539.458..10,883.991 rows=391,432 loops=1)

  • Recheck Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
  • Rows Removed by Index Recheck: 5320461
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 2498539
  • Heap Blocks: exact=97012 lossy=407092
13. 1,479.472 1,479.472 ↓ 1.0 3,184,353 1

Bitmap Index Scan on widgets_date_idx (cost=0.00..40,269.01 rows=3,087,677 width=0) (actual time=1,479.472..1,479.472 rows=3,184,353 loops=1)

  • Index Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
Planning time : 7.353 ms
Execution time : 11,024.851 ms