explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PZ8Y

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 87,223.585 ↑ 5.2 6 1

Limit (cost=50,436.28..50,436.30 rows=31 width=48) (actual time=87,223.584..87,223.585 rows=6 loops=1)

2.          

Initplan (for Limit)

3. 0.015 0.015 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1)

4. 0.004 0.004 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1)

5. 0.024 87,223.582 ↑ 5.2 6 1

Sort (cost=50,436.27..50,436.28 rows=31 width=48) (actual time=87,223.582..87,223.582 rows=6 loops=1)

  • Sort Key: (to_char((date_trunc('month'::text, timezone('America/New_York'::text, blog_page_views."time"))), 'YYYY-MM'::text)) DESC
  • Sort Method: quicksort Memory: 25kB
6. 151.747 87,223.558 ↑ 5.2 6 1

GroupAggregate (cost=50,435.90..50,436.11 rows=31 width=48) (actual time=87,096.301..87,223.558 rows=6 loops=1)

  • Group Key: (date_trunc('month'::text, timezone('America/New_York'::text, blog_page_views."time")))
7. 49.474 87,071.811 ↓ 2,864.5 88,800 1

Sort (cost=50,435.90..50,435.91 rows=31 width=53) (actual time=87,057.653..87,071.811 rows=88,800 loops=1)

  • Sort Key: (date_trunc('month'::text, timezone('America/New_York'::text, blog_page_views."time")))
  • Sort Method: quicksort Memory: 15,560kB
8. 83,861.233 87,022.337 ↓ 2,864.5 88,800 1

Nested Loop (cost=250.62..50,435.74 rows=31 width=53) (actual time=270.424..87,022.337 rows=88,800 loops=1)

  • Join Filter: (articles.id = ((blog_page_views.properties ->> 'id'::text))::integer)
  • Rows Removed by Join Filter: 41,558,400
9. 991.085 1,207.504 ↓ 53.0 88,800 1

Bitmap Heap Scan on ahoy_events blog_page_views (cost=248.42..49,915.26 rows=1,674 width=153) (actual time=268.204..1,207.504 rows=88,800 loops=1)

  • Recheck Cond: (("time" >= $0) AND ("time" < $1))
  • Filter: ((name)::text = 'Controller:blog_posts#show'::text)
  • Rows Removed by Filter: 1,719,533
  • Heap Blocks: exact=206,562
10. 216.419 216.419 ↓ 54.6 1,808,333 1

Bitmap Index Scan on ahoy_events_time_index (cost=0.00..248.34 rows=33,125 width=0) (actual time=216.419..216.419 rows=1,808,333 loops=1)

  • Index Cond: (("time" >= $0) AND ("time" < $1))
11. 1,952.291 1,953.600 ↓ 16.8 469 88,800

Materialize (cost=2.20..262.67 rows=28 width=4) (actual time=0.000..0.022 rows=469 loops=88,800)

12. 0.787 1.309 ↓ 16.8 469 1

Hash Join (cost=2.20..262.64 rows=28 width=4) (actual time=0.165..1.309 rows=469 loops=1)

  • Hash Cond: (articles.site_id = sites.id)
13. 0.502 0.502 ↑ 1.0 1,519 1

Seq Scan on articles (cost=0.00..259.56 rows=1,519 width=8) (actual time=0.013..0.502 rows=1,519 loops=1)

14. 0.005 0.020 ↑ 1.0 1 1

Hash (cost=2.19..2.19 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on sites (cost=0.00..2.19 rows=1 width=4) (actual time=0.009..0.015 rows=1 loops=1)

  • Filter: ((name)::text = 'Blog'::text)
  • Rows Removed by Filter: 54