explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.148 5,220.399 ↑ 10.2 16 1

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

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

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

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

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

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

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

5. 1.071 5.226 ↑ 10.2 16 1

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

  • Group Key: campaigns.date
6. 4.155 4.155 ↑ 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.532..4.155 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.015 0.030 ↑ 11.0 1 1

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

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

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

9. 0.035 5,214.951 ↑ 10.2 16 1

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

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

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

11. 179.659 5,214.913 ↑ 10.2 16 1

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

  • Group Key: widgets.date
12. 4,105.972 5,035.254 ↑ 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=1,172.554..5,035.254 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: 3263889
  • Heap Blocks: exact=549075
13. 929.282 929.282 ↓ 1.1 4,135,152 1

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

  • Index Cond: ((date >= '2019-05-28'::date) AND (date <= '2019-06-12'::date))
Planning time : 0.602 ms
Execution time : 5,220.558 ms