explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6D52

Settings
# exclusive inclusive rows x rows loops node
1. 4.507 30,403.292 ↓ 2.0 2 1

GroupAggregate (cost=264,112.89..264,112.98 rows=1 width=168) (actual time=30,402.729..30,403.292 rows=2 loops=1)

  • Group Key: (timezone('MSK'::text, date_trunc('month'::text, user_hourly_statistics.start_time)))
2. 5.139 30,398.785 ↓ 2,581.0 2,581 1

Sort (cost=264,112.89..264,112.90 rows=1 width=31) (actual time=30,398.145..30,398.785 rows=2,581 loops=1)

  • Sort Key: (timezone('MSK'::text, date_trunc('month'::text, user_hourly_statistics.start_time)))
  • Sort Method: quicksort Memory: 298kB
3. 14.146 30,393.646 ↓ 2,581.0 2,581 1

Nested Loop (cost=8,620.63..264,112.88 rows=1 width=31) (actual time=1,752.746..30,393.646 rows=2,581 loops=1)

4. 27,776.687 28,700.976 ↓ 218.8 3,063 1

Bitmap Heap Scan on users (cost=8,620.19..261,690.83 rows=14 width=8) (actual time=962.941..28,700.976 rows=3,063 loops=1)

  • Recheck Cond: ((created_at >= '2019-12-01 00:00:00'::timestamp without time zone) AND (created_at <= '2019-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Index Recheck: 559598
  • Filter: ((iphone OR ipad) AND ((install_source)::text = 'testbonus'::text))
  • Rows Removed by Filter: 124322
  • Heap Blocks: exact=45376 lossy=27141
5. 924.289 924.289 ↑ 1.0 129,178 1

Bitmap Index Scan on index_users_on_created_at (cost=0.00..8,620.19 rows=133,963 width=0) (actual time=924.289..924.289 rows=129,178 loops=1)

  • Index Cond: ((created_at >= '2019-12-01 00:00:00'::timestamp without time zone) AND (created_at <= '2019-12-31 00:00:00'::timestamp without time zone))
6. 1,678.524 1,678.524 ↑ 3.0 1 3,063

Index Scan using index_user_hourly_statistics_on_user_id on user_hourly_statistics (cost=0.44..172.97 rows=3 width=23) (actual time=0.231..0.548 rows=1 loops=3,063)

  • Index Cond: (user_id = users.id)
  • Filter: ((start_time >= '2020-01-01 00:00:00'::timestamp without time zone) AND (start_time <= '2020-02-14 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 3
Planning time : 4.398 ms
Execution time : 30,407.111 ms