explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sMX3

Settings
# exclusive inclusive rows x rows loops node
1. 0.885 4,397.572 ↑ 1.0 1 1

Aggregate (cost=3,725.50..3,725.51 rows=1 width=8) (actual time=4,397.571..4,397.572 rows=1 loops=1)

2. 1.188 4,396.687 ↓ 3.3 2,555 1

Hash Left Join (cost=2,493.93..3,723.54 rows=785 width=4) (actual time=4,376.137..4,396.687 rows=2,555 loops=1)

  • Hash Cond: ("user".id = user_operations_teams.user_id)
3. 8.979 4,395.119 ↓ 3.3 2,555 1

Hash Join (cost=2,471.74..3,695.30 rows=785 width=4) (actual time=4,375.750..4,395.119 rows=2,555 loops=1)

  • Hash Cond: ((current_org.user_id = "user".id) AND (current_org.org_id = "user".default_org_id))
4. 11.028 11.028 ↓ 1.0 33,054 1

Seq Scan on user_orgs current_org (cost=0.00..978.88 rows=31,578 width=8) (actual time=0.006..11.028 rows=33,054 loops=1)

  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 9554
5. 1.474 4,375.112 ↑ 2.4 2,555 1

Hash (cost=2,377.86..2,377.86 rows=6,259 width=12) (actual time=4,375.112..4,375.112 rows=2,555 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 174kB
6. 31.447 4,373.638 ↑ 2.4 2,555 1

Hash Join (cost=1,669.25..2,377.86 rows=6,259 width=12) (actual time=170.088..4,373.638 rows=2,555 loops=1)

  • Hash Cond: (user_orgs.user_id = "user".id)
  • Join Filter: ((hashed SubPlan 1) OR (SubPlan 2))
  • Rows Removed by Join Filter: 8625
7. 11.029 11.029 ↓ 1.4 12,086 1

Index Scan using idx_user_orgs_gits_role on user_orgs (cost=0.29..685.97 rows=8,736 width=8) (actual time=0.045..11.029 rows=12,086 loops=1)

  • Index Cond: ((role)::text = ANY ('{admin,orgadmin,superadmin,mastersuperadmin,productionOperation,"Scheduling admin",org_superadmin,riderAdmin,riderUser,networkOperation,providerManager,org_reporting,oc_reporting}'::text[]))
  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 682
8. 9.692 20.714 ↓ 1.0 32,589 1

Hash (cost=939.30..939.30 rows=32,513 width=8) (actual time=20.713..20.714 rows=32,589 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1505kB
9. 11.022 11.022 ↓ 1.0 32,589 1

Index Only Scan using users_id_is_del_default_org_id on users "user" (cost=0.29..939.30 rows=32,513 width=8) (actual time=0.017..11.022 rows=32,589 loops=1)

  • Index Cond: (is_del = 0)
  • Heap Fetches: 5002
10.          

SubPlan (for Hash Join)

11. 2.082 2.082 ↓ 1.3 3,381 1

Index Scan using user_orgs_org_id on user_orgs user_orgs_1 (cost=0.29..316.66 rows=2,631 width=4) (actual time=0.021..2.082 rows=3,381 loops=1)

  • Index Cond: (org_id = 4146)
  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 396
12. 8.634 4,308.366 ↓ 0.0 0 8,634

Bitmap Heap Scan on ride_details (cost=315.59..334.88 rows=19 width=4) (actual time=0.499..0.499 rows=0 loops=8,634)

  • Recheck Cond: ((ride_requester_id = "user".id) AND (org_id = 4146))
  • Heap Blocks: exact=9
13. 51.165 4,299.732 ↓ 0.0 0 8,634

BitmapAnd (cost=315.59..315.59 rows=19 width=0) (actual time=0.498..0.498 rows=0 loops=8,634)

14. 164.046 164.046 ↑ 8.2 102 8,634

Bitmap Index Scan on ride_details_ride_requester_id (cost=0.00..14.71 rows=838 width=0) (actual time=0.019..0.019 rows=102 loops=8,634)

  • Index Cond: (ride_requester_id = "user".id)
15. 4,084.521 4,084.521 ↑ 1.0 17,839 1,407

Bitmap Index Scan on idx_ride_organisationid (cost=0.00..300.62 rows=18,293 width=0) (actual time=2.903..2.903 rows=17,839 loops=1,407)

  • Index Cond: (org_id = 4146)
16. 0.182 0.380 ↑ 1.0 675 1

Hash (cost=13.75..13.75 rows=675 width=8) (actual time=0.380..0.380 rows=675 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
17. 0.198 0.198 ↑ 1.0 675 1

Seq Scan on user_operations_teams (cost=0.00..13.75 rows=675 width=8) (actual time=0.010..0.198 rows=675 loops=1)