explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5HJu

Settings
# exclusive inclusive rows x rows loops node
1. 25.049 31,937.923 ↓ 1,392.0 1,392 1

Hash Right Join (cost=234,743.70..236,366.08 rows=1 width=8) (actual time=31,937.329..31,937.923 rows=1,392 loops=1)

  • Hash Cond: (ad.user_id = ku.user_id)
  • Filter: ((ad.deleted IS NULL) OR (ad.deleted = 0))
  • Rows Removed by Filter: 13644
  • Buffers: shared hit=79704850 read=9105
2. 20.726 20.726 ↓ 1.0 77,161 1

Seq Scan on agent_details ad (cost=0.00..1,328.52 rows=76,852 width=10) (actual time=0.008..20.726 rows=77,161 loops=1)

  • Buffers: shared hit=74 read=486
3. 10.948 31,892.148 ↓ 4.9 15,036 1

Hash (cost=234,705.69..234,705.69 rows=3,041 width=8) (actual time=31,892.148..31,892.148 rows=15,036 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 716kB
  • Buffers: shared hit=79704773 read=8619
4. 250.808 31,881.200 ↓ 4.9 15,036 1

Bitmap Heap Scan on konotor_user ku (cost=195.83..234,705.69 rows=3,041 width=8) (actual time=233.194..31,881.200 rows=15,036 loops=1)

  • Recheck Cond: (((app_id = '231180530985826'::bigint) AND (user_type = 1)) OR ((app_id = '231180530985826'::bigint) AND (user_type = 2)))
  • Filter: (NOT (SubPlan 1))
  • Heap Blocks: exact=7474
  • Buffers: shared hit=79704773 read=8619
5. 0.002 9.684 ↓ 0.0 0 1

BitmapOr (cost=195.83..195.83 rows=6,145 width=0) (actual time=9.684..9.684 rows=0 loops=1)

  • Buffers: shared hit=8 read=74
6. 0.055 0.055 ↑ 351.0 1 1

Bitmap Index Scan on konotor_user_app_id_user_type_idx (cost=0.00..11.93 rows=351 width=0) (actual time=0.055..0.055 rows=1 loops=1)

  • Index Cond: ((app_id = '231180530985826'::bigint) AND (user_type = 1))
  • Buffers: shared hit=4 read=2
7. 9.627 9.627 ↓ 2.6 15,065 1

Bitmap Index Scan on konotor_user_app_id_user_type_idx (cost=0.00..182.37 rows=5,795 width=0) (actual time=9.627..9.627 rows=15,065 loops=1)

  • Index Cond: ((app_id = '231180530985826'::bigint) AND (user_type = 2))
  • Buffers: shared hit=4 read=72
8.          

SubPlan (for Bitmap Heap Scan)

9. 15.036 31,620.708 ↓ 0.0 0 15,036

Nested Loop (cost=0.70..71.29 rows=1 width=8) (actual time=2.103..2.103 rows=0 loops=15,036)

  • Buffers: shared hit=79704515 read=1321
10. 31,605.672 31,605.672 ↓ 0.0 0 15,036

Index Scan using groups_app_id_group_id_idx on groups (cost=0.42..58.67 rows=2 width=8) (actual time=2.102..2.102 rows=0 loops=15,036)

  • Index Cond: (app_id = ku.app_id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 6243
  • Buffers: shared hit=79704515 read=1321
11. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uk_groupid_userid on group_user gu (cost=0.28..6.30 rows=1 width=16) (never executed)

  • Index Cond: ((group_id = groups.group_id) AND (user_id = ku.user_id))
  • Heap Fetches: 0
Planning time : 19.202 ms
Execution time : 31,938.375 ms