explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pXI7 : 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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.142 4,230.801 ↑ 10.2 16 1

Merge Join (cost=1,351,918.86..1,351,930.84 rows=163 width=148) (actual time=4,230.693..4,230.801 rows=16 loops=1)

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

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

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

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

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

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

5. 1.217 3.831 ↑ 10.2 16 1

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

  • Group Key: campaigns.date
6. 2.614 2.614 ↑ 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.015..2.614 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.013 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.008 0.008 ↑ 1.0 11 1

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

9. 0.020 4,226.771 ↑ 10.2 16 1

Sort (cost=1,349,946.14..1,349,946.55 rows=163 width=12) (actual time=4,226.766..4,226.771 rows=16 loops=1)

  • Sort Key: wid.date DESC
  • Sort Method: quicksort Memory: 25kB
10. 0.005 4,226.751 ↑ 10.2 16 1

Subquery Scan on wid (cost=1,349,936.89..1,349,940.15 rows=163 width=12) (actual time=4,226.747..4,226.751 rows=16 loops=1)

11. 166.568 4,226.746 ↑ 10.2 16 1

HashAggregate (cost=1,349,936.89..1,349,938.52 rows=163 width=20) (actual time=4,226.745..4,226.746 rows=16 loops=1)

  • Group Key: widgets.date
12. 3,443.888 4,060.178 ↑ 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=860.826..4,060.178 rows=649,564 loops=1)

  • Recheck Cond: ((date >= '2019-05-28'::date) AND (date <= '2019-06-12'::date))
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 3263890
  • Heap Blocks: exact=549076
13. 616.290 616.290 ↓ 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=616.290..616.290 rows=4,135,153 loops=1)

  • Index Cond: ((date >= '2019-05-28'::date) AND (date <= '2019-06-12'::date))
Planning time : 0.519 ms
Execution time : 4,230.916 ms