explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NBVB

Settings
# exclusive inclusive rows x rows loops node
1. 0.150 134.049 ↓ 47.0 658 1

Nested Loop Anti Join (cost=0.29..108,854.41 rows=14 width=16) (actual time=0.135..134.049 rows=658 loops=1)

2. 0.738 0.738 ↓ 1.1 1,119 1

Seq Scan on onboarding_status os (cost=0.00..156.01 rows=1,025 width=16) (actual time=0.010..0.738 rows=1,119 loops=1)

  • Filter: (status = 'AWAITING_REVIEW'::onboarding_state)
  • Rows Removed by Filter: 5700
3. 7.659 133.161 ↓ 0.0 0 1,119

Index Scan using idx_onboarding_check_person_uid on onboarding_check oc1 (cost=0.29..221.12 rows=3 width=16) (actual time=0.119..0.119 rows=0 loops=1,119)

  • Index Cond: (person_uid = os.person_uid)
  • Filter: (NOT (SubPlan 1))
  • Rows Removed by Filter: 4
4.          

SubPlan (forIndex Scan)

5. 4.827 125.502 ↑ 1.0 1 4,827

GroupAggregate (cost=36.26..36.29 rows=1 width=40) (actual time=0.026..0.026 rows=1 loops=4,827)

  • Group Key: oc2.check_uid, onboarding_required_approvals.approvals_required
  • Filter: ((count(*) FILTER (WHERE od.passed) >= onboarding_required_approvals.approvals_required) AND (count(*) FILTER (WHERE (od.passed IS NULL)) = 0))
  • Rows Removed by Filter: 0
6. 14.481 120.675 ↓ 3.0 3 4,827

Sort (cost=36.26..36.27 rows=1 width=41) (actual time=0.025..0.025 rows=3 loops=4,827)

  • Sort Key: oc2.check_uid, onboarding_required_approvals.approvals_required
  • Sort Method: quicksort Memory: 25kB
7. 28.901 106.194 ↓ 4.0 4 4,827

Nested Loop (cost=4.75..36.25 rows=1 width=41) (actual time=0.009..0.022 rows=4 loops=4,827)

  • Join Filter: (oc2.review_team = (onboarding_required_approvals.review_team)::text)
  • Rows Removed by Join Filter: 0
8. 21.347 57.924 ↓ 4.0 4 4,827

Nested Loop Left Join (cost=4.75..35.02 rows=1 width=48) (actual time=0.007..0.012 rows=4 loops=4,827)

9. 9.654 14.481 ↓ 2.0 2 4,827

Bitmap Heap Scan on onboarding_check oc2 (cost=4.34..26.56 rows=1 width=47) (actual time=0.003..0.003 rows=2 loops=4,827)

  • Recheck Cond: (oc1.person_uid = person_uid)
  • Filter: (check_type = oc1.check_type)
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=5091
10. 4.827 4.827 ↑ 1.0 6 4,827

Bitmap Index Scan on idx_onboarding_check_person_uid (cost=0.00..4.33 rows=6 width=0) (actual time=0.001..0.001 rows=6 loops=4,827)

  • Index Cond: (oc1.person_uid = person_uid)
11. 22.096 22.096 ↓ 2.0 2 11,048

Index Only Scan using uk_onboarding_decision on onboarding_decision od (cost=0.41..8.45 rows=1 width=21) (actual time=0.002..0.002 rows=2 loops=11,048)

  • Index Cond: ((check_uid = oc2.check_uid) AND (check_type = oc2.check_type) AND (check_type = oc1.check_type))
  • Heap Fetches: 18975
12. 19.369 19.369 ↑ 3.0 1 19,369

Seq Scan on onboarding_required_approvals (cost=0.00..1.20 rows=3 width=21) (actual time=0.001..0.001 rows=1 loops=19,369)

  • Filter: (check_type = oc1.check_type)
  • Rows Removed by Filter: 1