explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dFZJ : excluding-6134

Settings
# exclusive inclusive rows x rows loops node
1. 33.356 24,137.224 ↑ 128.4 6,134 1

Nested Loop (cost=1,797.96..5,748,222.62 rows=787,518 width=24) (actual time=197.186..24,137.224 rows=6,134 loops=1)

  • Output: t1.x, t1.y, t1.id
  • Buffers: shared hit=15991 read=18113, local hit=384
2. 1,500.867 1,561.418 ↑ 128.4 6,134 1

Index Only Scan using idx_ftweets_create_at_x_y_id on public.ftweets t2 (cost=1,794.80..87,297.88 rows=787,518 width=8) (actual time=163.145..1,561.418 rows=6,134 loops=1)

  • Output: t2.create_at, t2.x, t2.y, t2.id
  • Index Cond: ((t2.create_at >= '2017-02-09 08:00:00'::timestamp without time zone) AND (t2.create_at < '2017-03-11 08:00:00'::timestamp without time zone))
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 1533270
  • Heap Fetches: 0
  • Buffers: shared hit=259 read=9280, local hit=384
3.          

SubPlan (forIndex Only Scan)

4. 50.745 60.551 ↓ 10.0 86,744 1

HashAggregate (cost=1,685.76..1,772.54 rows=8,678 width=8) (actual time=44.618..60.551 rows=86,744 loops=1)

  • Output: ftweets_exclude.bx, ftweets_exclude.by
  • Group Key: ftweets_exclude.bx, ftweets_exclude.by
  • Buffers: local hit=384
5. 9.806 9.806 ↑ 1.0 86,744 1

Seq Scan on pg_temp_3.ftweets_exclude (cost=0.00..1,251.84 rows=86,784 width=8) (actual time=0.014..9.806 rows=86,744 loops=1)

  • Output: ftweets_exclude.bx, ftweets_exclude.by
  • Buffers: local hit=384
6. 21,738.896 22,542.450 ↑ 1.0 1 6,134

Bitmap Heap Scan on public.ftweets t1 (cost=3.17..7.18 rows=1 width=24) (actual time=3.674..3.675 rows=1 loops=6,134)

  • Output: t1.create_at, t1.id, t1.text, t1.x, t1.y, t1.in_reply_to_status, t1.in_reply_to_user, t1.favorite_count, t1.retweet_count, t1.lang, t1.is_retweet, t1.user_id, t1.user_name, t1.user_screen_name, t1.user_profile_image_url, t1.user_lang, t1.user_location, t1.user_create_at, t1.user_description, t1.user_followers_count, t1.user_friends_count, t1.user_statues_count, t1.place_country, t1.place_country_code, t1.place_full_name, t1.place_id, t1.place_name, t1.place_place_type, t1.geo_tag_state_id, t1.geo_tag_state_name, t1.geo_tag_county_id, t1.geo_tag_county_name, t1.geo_tag_city_id, t1.geo_tag_city_name
  • Recheck Cond: (t1.id = t2.id)
  • Heap Blocks: exact=6134
  • Buffers: shared hit=15732 read=8833
7. 803.554 803.554 ↑ 1.0 1 6,134

Bitmap Index Scan on ftweets_pkey (cost=0.00..3.17 rows=1 width=0) (actual time=0.131..0.131 rows=1 loops=6,134)

  • Index Cond: (t1.id = t2.id)
  • Buffers: shared hit=15382 read=3049