explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vtgQd : Optimization for: DH; plan #vPnF

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 29.159 118.229 ↓ 2,000.0 2,000 1

Sort (cost=991.22..991.23 rows=1 width=1,671) (actual time=117.438..118.229 rows=2,000 loops=1)

  • Sort Key: ku.email
  • Sort Method: quicksort Memory: 1,080kB
  • Buffers: shared hit=38,568 read=75
2.          

CTE user_ids

3. 0.871 10.203 ↓ 2,000.0 2,000 1

Limit (cost=770.25..968.98 rows=1 width=10) (actual time=8.453..10.203 rows=2,000 loops=1)

  • Buffers: shared hit=549 read=66
4. 3.320 9.332 ↓ 2,000.0 2,000 1

Hash Join (cost=770.25..968.98 rows=1 width=10) (actual time=8.453..9.332 rows=2,000 loops=1)

  • Filter: ((ad.deleted IS NULL) OR (ad.deleted = 0))
  • Buffers: shared hit=549 read=66
5. 2.464 2.464 ↓ 1.0 9,069 1

Seq Scan on agent_details ad (cost=0..160.37 rows=9,037 width=10) (actual time=0.004..2.464 rows=9,069 loops=1)

  • Buffers: shared hit=4 read=66
6. 0.608 3.548 ↓ 6.2 2,202 1

Hash (cost=765.78..765.78 rows=358 width=8) (actual time=3.548..3.548 rows=2,202 loops=1)

  • Buffers: shared hit=545
7. 1.312 2.940 ↓ 6.2 2,202 1

Sort (cost=761.3..762.2 rows=358 width=8) (actual time=2.437..2.94 rows=2,202 loops=1)

  • Sort Key: ko.user_id
  • Sort Method: quicksort Memory: 200kB
  • Buffers: shared hit=545
8. 1.628 1.628 ↓ 6.2 2,202 1

Index Scan using konotor_user_app_id_user_type_idx on konotor_user ko (cost=0.42..746.11 rows=358 width=8) (actual time=0.009..1.628 rows=2,202 loops=1)

  • Index Cond: ((app_id = '397514621453913'::bigint) AND (user_type = ANY ('{1,2}'::integer[])))
  • Filter: (NOT hard_deleted)
  • Buffers: shared hit=545
9. 2.602 89.070 ↓ 2,000.0 2,000 1

Nested Loop (cost=2.12..22.23 rows=1 width=1,671) (actual time=8.518..89.07 rows=2,000 loops=1)

  • Buffers: shared hit=38,568 read=75
10. 2.078 86.468 ↓ 2,000.0 2,000 1

Nested Loop (cost=1.71..21.29 rows=1 width=1,612) (actual time=8.516..86.468 rows=2,000 loops=1)

  • Buffers: shared hit=38,568 read=75
11. 2.637 80.390 ↓ 2,000.0 2,000 1

Nested Loop (cost=1.43..20.93 rows=1 width=1,561) (actual time=8.51..80.39 rows=2,000 loops=1)

  • Buffers: shared hit=32,568 read=75
12. 1.707 77.753 ↓ 2,000.0 2,000 1

Nested Loop (cost=1.15..20.31 rows=1 width=1,524) (actual time=8.509..77.753 rows=2,000 loops=1)

  • Buffers: shared hit=32,568 read=75
13. 2.825 32.046 ↓ 2,000.0 2,000 1

Nested Loop (cost=0.87..8.99 rows=1 width=1,516) (actual time=8.48..32.046 rows=2,000 loops=1)

  • Buffers: shared hit=18,568 read=75
14. 2.244 25.221 ↓ 2,000.0 2,000 1

Nested Loop (cost=0.58..8.67 rows=1 width=1,162) (actual time=8.474..25.221 rows=2,000 loops=1)

  • Buffers: shared hit=12,559 read=75
15. 1.640 18.977 ↓ 2,000.0 2,000 1

Nested Loop (cost=0.29..8.34 rows=1 width=1,158) (actual time=8.469..18.977 rows=2,000 loops=1)

  • Buffers: shared hit=6,551 read=75
16. 11.337 11.337 ↓ 2,000.0 2,000 1

CTE Scan on user_ids user_ids (cost=0..0.02 rows=1 width=10) (actual time=8.456..11.337 rows=2,000 loops=1)

  • Buffers: shared hit=549 read=66
17. 6.000 6.000 ↑ 1.0 1 2,000

Index Scan using user_pkey on konotor_user ku (cost=0.29..8.31 rows=1 width=1,156) (actual time=0.002..0.003 rows=1 loops=2,000)

  • Index Cond: (user_id = user_ids.user_id)
  • Buffers: shared hit=6,002 read=9
18. 4.000 4.000 ↑ 1.0 1 2,000

Index Scan using uk_userid on user_role ur (cost=0.29..0.31 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=2,000)

  • Index Cond: (ku.user_id = user_id)
  • Buffers: shared hit=6,008
19. 4.000 4.000 ↑ 1.0 1 2,000

Index Scan using agent_props_pkey on agent_props ap (cost=0.29..0.32 rows=1 width=362) (actual time=0.002..0.002 rows=1 loops=2,000)

  • Index Cond: (ku.user_id = user_id)
  • Buffers: shared hit=6,009
20. 44.000 44.000 ↓ 0.0 0 2,000

Index Only Scan using uk_groupid_userid on group_user gu (cost=0.28..11.29 rows=2 width=16) (actual time=0.022..0.022 rows=0 loops=2,000)

  • Index Cond: (user_id = ku.user_id)
  • Buffers: shared hit=14,000
21. 0.000 0.000 ↓ 0.0 0 2,000

Index Scan using groups_pkey on groups grp (cost=0.28..0.61 rows=1 width=45) (actual time=0..0 rows=0 loops=2,000)

  • Index Cond: (gu.group_id = group_id)
22. 4.000 4.000 ↑ 1.0 1 2,000

Index Scan using role_pkey on role ro (cost=0.28..0.35 rows=1 width=59) (actual time=0.001..0.002 rows=1 loops=2,000)

  • Index Cond: (role_id = ur.role_id)
  • Buffers: shared hit=6,000
23. 0.000 0.000 ↓ 0.0 0 2,000

Index Scan using experience_group_limits_exp_group_id_key on experience_group_limits egl (cost=0.41..0.93 rows=1 width=45) (actual time=0..0 rows=0 loops=2,000)

  • Index Cond: (exp_group_id = ap.exp_group_id)
Planning time : 2.373 ms
Execution time : 118.81 ms