explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M04V : Optimization for: plan #dIBt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,877.337 4,245.289 ↓ 4,804.0 4,804 1

Nested Loop (cost=2,455.31..3,085.09 rows=1 width=569) (actual time=8.482..4,245.289 rows=4,804 loops=1)

  • Join Filter: (u.id = i.user_id)
  • Rows Removed by Join Filter: 8123235
2.          

CTE fj

3. 7.987 7.987 ↓ 1.0 12,962 1

Seq Scan on food_journals (cost=0.00..1,225.65 rows=12,915 width=73) (actual time=0.005..7.987 rows=12,962 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 250
4. 5.953 68.477 ↓ 4,841.0 4,841 1

Nested Loop (cost=1,229.66..1,792.48 rows=1 width=580) (actual time=8.082..68.477 rows=4,841 loops=1)

5. 9.998 47.107 ↓ 5,139.0 5,139 1

Hash Left Join (cost=1,229.38..1,792.06 rows=1 width=552) (actual time=8.052..47.107 rows=5,139 loops=1)

  • Hash Cond: (fj.id = ec.entry_id)
  • Filter: (ec.id IS NULL)
  • Rows Removed by Filter: 10327
6. 29.099 29.099 ↓ 3.0 12,962 1

CTE Scan on fj (cost=0.00..419.74 rows=4,305 width=560) (actual time=0.017..29.099 rows=12,962 loops=1)

  • Filter: (date_part('hour'::text, (('now'::cstring)::timestamp without time zone - inserted_at)) < '720'::double precision)
7. 3.595 8.010 ↓ 1.0 10,344 1

Hash (cost=1,100.28..1,100.28 rows=10,328 width=40) (actual time=8.010..8.010 rows=10,344 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 856kB
8. 4.415 4.415 ↓ 1.0 10,344 1

Seq Scan on entry_comments ec (cost=0.00..1,100.28 rows=10,328 width=40) (actual time=0.005..4.415 rows=10,344 loops=1)

9. 15.417 15.417 ↑ 1.0 1 5,139

Index Scan using users_pkey on users u (cost=0.28..0.42 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=5,139)

  • Index Cond: (id = fj.user_id)
  • Filter: (NOT dummy)
  • Rows Removed by Filter: 0
10. 2,299.475 2,299.475 ↑ 1.0 1,679 4,841

Seq Scan on invitations i (cost=0.00..45.31 rows=1,731 width=29) (actual time=0.002..0.475 rows=1,679 loops=4,841)