explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.356 10,518.050 ↑ 13.6 12 1

Merge Join (cost=1,323,566.42..1,323,578.41 rows=163 width=148) (actual time=10,517.965..10,518.050 rows=12 loops=1)

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

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

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

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

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

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

5. 0.900 3.907 ↑ 13.6 12 1

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

  • Group Key: campaigns.date
6. 3.007 3.007 ↑ 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.307..3.007 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.016 0.274 ↑ 11.0 1 1

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

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

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

9. 0.034 10,513.474 ↑ 13.6 12 1

Sort (cost=1,321,885.57..1,321,885.98 rows=163 width=12) (actual time=10,513.473..10,513.474 rows=12 loops=1)

  • Sort Key: wid.date DESC
  • Sort Method: quicksort Memory: 25kB
10. 0.006 10,513.440 ↑ 13.6 12 1

Subquery Scan on wid (cost=1,321,876.32..1,321,879.58 rows=163 width=12) (actual time=10,513.434..10,513.440 rows=12 loops=1)

11. 127.426 10,513.434 ↑ 13.6 12 1

HashAggregate (cost=1,321,876.32..1,321,877.95 rows=163 width=20) (actual time=10,513.433..10,513.434 rows=12 loops=1)

  • Group Key: widgets.date
12. 9,347.959 10,386.008 ↑ 6.4 391,519 1

Bitmap Heap Scan on widgets (cost=40,536.36..1,309,432.29 rows=2,488,805 width=8) (actual time=1,090.874..10,386.008 rows=391,519 loops=1)

  • Recheck Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
  • Rows Removed by Index Recheck: 5295355
  • Filter: (source_id = 12)
  • Rows Removed by Filter: 2498540
  • Heap Blocks: exact=91989 lossy=407086
13. 1,038.049 1,038.049 ↑ 1.0 2,972,595 1

Bitmap Index Scan on widgets_date_idx (cost=0.00..39,914.16 rows=3,049,882 width=0) (actual time=1,038.049..1,038.049 rows=2,972,595 loops=1)

  • Index Cond: ((date >= '2019-06-01'::date) AND (date <= '2019-06-12'::date))
Planning time : 2.914 ms
Execution time : 10,518.201 ms