explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xcpI : Optimization for: plan #dIBt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,909.922 8,689.620 ↓ 4,804.0 4,804 1

Nested Loop (cost=2,581.29..3,266.40 rows=1 width=1,073) (actual time=12.783..8,689.620 rows=4,804 loops=1)

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

CTE fj

3. 8.450 8.450 ↓ 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..8.450 rows=12,962 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 250
4.          

CTE users1

5. 0.795 0.795 ↑ 1.0 1,707 1

Seq Scan on users (cost=0.00..126.26 rows=1,707 width=28) (actual time=0.004..0.795 rows=1,707 loops=1)

  • Filter: (NOT dummy)
  • Rows Removed by Filter: 19
6. 2,238.981 4,509.269 ↓ 4,841.0 4,841 1

Nested Loop (cost=1,229.38..1,847.53 rows=1 width=1,084) (actual time=12.384..4,509.269 rows=4,841 loops=1)

  • Join Filter: (fj.user_id = u.id)
  • Rows Removed by Join Filter: 8767432
7. 10.715 50.240 ↓ 5,139.0 5,139 1

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

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

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

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 856kB
10. 4.439 4.439 ↓ 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.439 rows=10,344 loops=1)

11. 2,220.048 2,220.048 ↑ 1.0 1,707 5,139

CTE Scan on users1 u (cost=0.00..34.14 rows=1,707 width=532) (actual time=0.000..0.432 rows=1,707 loops=5,139)

12. 2,270.429 2,270.429 ↑ 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.469 rows=1,679 loops=4,841)