explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FDqQM : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #X7OV; plan #4qTb; plan #oZWp; plan #Vm5S; plan #Xoct; plan #bxGg; plan #DmE2; plan #pXI7

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.153 5,997.585 ↑ 10.2 16 1

Merge Join (cost=1,351,918.86..1,351,930.84 rows=163 width=148) (actual time=5,997.478..5,997.585 rows=16 loops=1)

  • Merge Cond: (b.date = wid.date)
2. 0.010 3.936 ↑ 10.2 16 1

Merge Left Join (cost=1,972.72..1,973.70 rows=163 width=92) (actual time=3.924..3.936 rows=16 loops=1)

  • Merge Cond: (b.date = n.date)
3. 0.020 3.905 ↑ 10.2 16 1

Sort (cost=1,971.42..1,971.83 rows=163 width=60) (actual time=3.899..3.905 rows=16 loops=1)

  • Sort Key: b.date DESC
  • Sort Method: quicksort Memory: 27kB
4. 0.007 3.885 ↑ 10.2 16 1

Subquery Scan on b (cost=0.29..1,965.43 rows=163 width=60) (actual time=0.283..3.885 rows=16 loops=1)

5. 1.010 3.878 ↑ 10.2 16 1

GroupAggregate (cost=0.29..1,963.80 rows=163 width=64) (actual time=0.282..3.878 rows=16 loops=1)

  • Group Key: campaigns.date
6. 2.868 2.868 ↑ 1.0 3,599 1

Index Scan using boosts_pkey on campaigns (cost=0.29..1,859.62 rows=3,670 width=32) (actual time=0.019..2.868 rows=3,599 loops=1)

  • Index Cond: ((date >= '2019-05-28'::date) AND (date <= '2019-06-12'::date))
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 436
7. 0.008 0.021 ↑ 11.0 1 1

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

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

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

9. 0.016 5,993.496 ↑ 10.2 16 1

Sort (cost=1,349,946.14..1,349,946.55 rows=163 width=12) (actual time=5,993.494..5,993.496 rows=16 loops=1)

  • Sort Key: wid.date DESC
  • Sort Method: quicksort Memory: 25kB
10. 0.003 5,993.480 ↑ 10.2 16 1

Subquery Scan on wid (cost=1,349,936.89..1,349,940.15 rows=163 width=12) (actual time=5,993.473..5,993.480 rows=16 loops=1)

11. 167.648 5,993.477 ↑ 10.2 16 1

HashAggregate (cost=1,349,936.89..1,349,938.52 rows=163 width=20) (actual time=5,993.473..5,993.477 rows=16 loops=1)

  • Group Key: widgets.date
12. 5,095.117 5,825.829 ↑ 4.8 649,564 1

Bitmap Heap Scan on widgets (cost=51,666.98..1,334,203.93 rows=3,146,592 width=8) (actual time=770.637..5,825.829 rows=649,564 loops=1)

  • Recheck Cond: ((date >= '2019-05-28'::date) AND (date <= '2019-06-12'::date))
  • Rows Removed by Index Recheck: 8240312
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 3263890
  • Heap Blocks: exact=74466 lossy=474610
13. 730.712 730.712 ↓ 1.1 4,135,153 1

Bitmap Index Scan on widgets_date_idx (cost=0.00..50,880.33 rows=3,829,369 width=0) (actual time=730.712..730.712 rows=4,135,153 loops=1)

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