explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yoxy : excluding-12326

Settings
# exclusive inclusive rows x rows loops node
1. 52.240 13,614.523 ↑ 45.2 12,326 1

Nested Loop (cost=1,199.89..4,066,058.67 rows=556,790 width=24) (actual time=139.550..13,614.523 rows=12,326 loops=1)

  • Output: t1.x, t1.y, t1.id
  • Buffers: shared hit=35474 read=21066, local hit=256
2. 1,444.601 1,470.477 ↑ 45.2 12,326 1

Index Only Scan using idx_ftweets_create_at_x_y_id on public.ftweets t2 (cost=1,196.72..61,648.13 rows=556,790 width=8) (actual time=99.091..1,470.477 rows=12,326 loops=1)

  • Output: t2.create_at, t2.x, t2.y, t2.id
  • Index Cond: ((t2.create_at >= '2017-06-09 07:00:00'::timestamp without time zone) AND (t2.create_at < '2017-07-09 07:00:00'::timestamp without time zone))
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 1114395
  • Heap Fetches: 0
  • Buffers: shared hit=373 read=6792, local hit=256
3.          

SubPlan (forIndex Only Scan)

4. 22.129 25.876 ↓ 10.0 57,768 1

HashAggregate (cost=1,123.84..1,181.70 rows=5,786 width=8) (actual time=17.518..25.876 rows=57,768 loops=1)

  • Output: ftweets_exclude.bx, ftweets_exclude.by
  • Group Key: ftweets_exclude.bx, ftweets_exclude.by
  • Buffers: local hit=256
5. 3.747 3.747 ↑ 1.0 57,768 1

Seq Scan on pg_temp_3.ftweets_exclude (cost=0.00..834.56 rows=57,856 width=8) (actual time=0.020..3.747 rows=57,768 loops=1)

  • Output: ftweets_exclude.bx, ftweets_exclude.by
  • Buffers: local hit=256
6. 11,919.242 12,091.806 ↑ 1.0 1 12,326

Bitmap Heap Scan on public.ftweets t1 (cost=3.17..7.18 rows=1 width=24) (actual time=0.980..0.981 rows=1 loops=12,326)

  • 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=12326
  • Buffers: shared hit=35101 read=14274
7. 172.564 172.564 ↑ 1.0 1 12,326

Bitmap Index Scan on ftweets_pkey (cost=0.00..3.17 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=12,326)

  • Index Cond: (t1.id = t2.id)
  • Buffers: shared hit=34054 read=2995