explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JFok : test

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 27,868.285 ↓ 2.4 17 1

Limit (cost=70,997.01..70,997.03 rows=7 width=527) (actual time=27,868.259..27,868.285 rows=17 loops=1)

2. 30.933 27,868.262 ↓ 2.4 17 1

Sort (cost=70,997.01..70,997.03 rows=7 width=527) (actual time=27,868.257..27,868.262 rows=17 loops=1)

  • Sort Key: ride.appt_date_time
  • Sort Method: top-N heapsort Memory: 37kB
3. 513.016 27,837.329 ↓ 3,617.6 25,323 1

Nested Loop Left Join (cost=34,347.63..70,996.92 rows=7 width=527) (actual time=15,596.702..27,837.329 rows=25,323 loops=1)

4. 36.270 27,273.667 ↓ 3,617.6 25,323 1

Nested Loop Left Join (cost=34,331.83..70,871.10 rows=7 width=520) (actual time=15,596.624..27,273.667 rows=25,323 loops=1)

5. 3,658.962 27,060.136 ↓ 3,617.6 25,323 1

Nested Loop Left Join (cost=34,331.40..70,811.99 rows=7 width=465) (actual time=15,596.595..27,060.136 rows=25,323 loops=1)

  • Join Filter: (appointment.recurrence_id = recurrence_rides.id)
  • Rows Removed by Join Filter: 16,890,441
6. 36.330 20,109.184 ↓ 3,617.6 25,323 1

Nested Loop Left Join (cost=34,331.40..70,712.67 rows=7 width=463) (actual time=15,595.874..20,109.184 rows=25,323 loops=1)

7. 9,539.093 20,072.854 ↓ 3,617.6 25,323 1

Nested Loop (cost=34,330.98..70,670.18 rows=7 width=455) (actual time=15,595.846..20,072.854 rows=25,323 loops=1)

  • Join Filter: (appointment.org_id = org.id)
  • Rows Removed by Join Filter: 25,753,491
8. 2.551 2.551 ↓ 1.0 1,018 1

Index Scan using idx_main_contact_id on organisations org (cost=0.28..574.98 rows=1,007 width=77) (actual time=0.007..2.551 rows=1,018 loops=1)

9. 7,784.943 10,531.210 ↓ 3,617.6 25,323 1,018

Materialize (cost=34,330.71..69,989.49 rows=7 width=386) (actual time=0.139..10.345 rows=25,323 loops=1,018)

10. 39.782 2,746.267 ↓ 3,617.6 25,323 1

Nested Loop (cost=34,330.71..69,989.45 rows=7 width=386) (actual time=139.617..2,746.267 rows=25,323 loops=1)

11. 32.340 2,681.162 ↓ 3,617.6 25,323 1

Nested Loop (cost=34,330.29..69,930.96 rows=7 width=359) (actual time=139.609..2,681.162 rows=25,323 loops=1)

12. 30.738 2,395.592 ↓ 3,617.6 25,323 1

Nested Loop (cost=34,329.87..69,926.83 rows=7 width=332) (actual time=139.592..2,395.592 rows=25,323 loops=1)

13. 585.697 718.859 ↓ 516.8 25,323 1

Bitmap Heap Scan on ride_details ride (cost=34,329.44..69,512.23 rows=49 width=248) (actual time=139.067..718.859 rows=25,323 loops=1)

  • Recheck Cond: (((ride_status)::text = ANY ('{Confirmed,"Failed Fuse","Potential Unavailability","Pending Approval","Offer Created",UATP,NEPF,"Potential Unavailability","Pending Approval",NDA,"No Driver Assigned","Order Updated",Offered,"Pending - Re-Offer","24 Hour Confirmation Missing","Validation Failed","Offered NDA"}'::text[])) AND (org_id = ANY ('{1604,1605,1606,1721,1742,1743,1755,1756,1757,1810,1811,1813,1816,1825,1885}'::integer[])))
  • Rows Removed by Index Recheck: 77,130
  • Filter: ((NOT is_pr_ride) AND (NULLIF(leg_no, 1) IS NULL))
  • Rows Removed by Filter: 26,268
  • Heap Blocks: exact=34,115 lossy=28,717
14. 4.776 133.162 ↓ 0.0 0 1

BitmapAnd (cost=34,329.44..34,329.44 rows=9,737 width=0) (actual time=133.162..133.162 rows=0 loops=1)

15. 51.596 51.596 ↓ 5.4 333,151 1

Bitmap Index Scan on ride_status (cost=0.00..2,880.18 rows=61,457 width=0) (actual time=51.596..51.596 rows=333,151 loops=1)

  • Index Cond: ((ride_status)::text = ANY ('{Confirmed,"Failed Fuse","Potential Unavailability","Pending Approval","Offer Created",UATP,NEPF,"Potential Unavailability","Pending Approval",NDA,"No Driver Assigned","Order Updated",Offered,"Pending - Re-Offer","24 Hour Confirmation Missing","Validation Failed","Offered NDA"}'::text[]))
16. 76.790 76.790 ↓ 1.3 401,796 1

Bitmap Index Scan on idx_ride_organisationid (cost=0.00..31,448.99 rows=314,758 width=0) (actual time=76.790..76.790 rows=401,796 loops=1)

  • Index Cond: (org_id = ANY ('{1604,1605,1606,1721,1742,1743,1755,1756,1757,1810,1811,1813,1816,1825,1885}'::integer[]))
17. 1,645.995 1,645.995 ↑ 1.0 1 25,323

Index Scan using appointments_pkey on appointments appointment (cost=0.42..8.46 rows=1 width=84) (actual time=0.065..0.065 rows=1 loops=25,323)

  • Index Cond: (id = ride.appt_id)
  • Filter: (org_id = ANY ('{1604,1605,1606,1721,1742,1743,1755,1756,1757,1810,1811,1813,1816,1825,1885}'::integer[]))
18. 253.230 253.230 ↑ 1.0 1 25,323

Index Scan using patients_pkey on patients appt_patient (cost=0.42..0.59 rows=1 width=31) (actual time=0.010..0.010 rows=1 loops=25,323)

  • Index Cond: (id = appointment.patient_id)
19. 25.323 25.323 ↑ 1.0 1 25,323

Index Scan using patients_pkey on patients patient (cost=0.42..8.36 rows=1 width=31) (actual time=0.001..0.001 rows=1 loops=25,323)

  • Index Cond: (id = ride.patient_id)
20. 0.000 0.000 ↓ 0.0 0 25,323

Index Scan using users_pkey on users main_contact (cost=0.42..6.07 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=25,323)

  • Index Cond: (id = org.main_contact_id)
21. 3,291.749 3,291.990 ↓ 1.0 667 25,323

Materialize (cost=0.00..31.87 rows=658 width=6) (actual time=0.000..0.130 rows=667 loops=25,323)

22. 0.241 0.241 ↓ 1.0 667 1

Seq Scan on recurrence_rides (cost=0.00..28.58 rows=658 width=6) (actual time=0.007..0.241 rows=667 loops=1)

23. 177.261 177.261 ↑ 1.0 1 25,323

Index Scan using ride_detail_id_unique on ride_additional_details (cost=0.43..8.45 rows=1 width=59) (actual time=0.007..0.007 rows=1 loops=25,323)

  • Index Cond: (ride.id = ride_detail_id)
24. 0.000 50.646 ↓ 0.0 0 25,323

Index Scan using idx_phone_number_zip_zip_code on phone_number_zip (cost=15.80..17.95 rows=1 width=23) (actual time=0.002..0.002 rows=0 loops=25,323)

  • Index Cond: ((zip_code)::text = (SubPlan 2))
  • Filter: (org_id = ride.org_id)
25.          

SubPlan (for Index Scan)

26. 50.646 430.491 ↑ 1.0 1 25,323

Limit (cost=7.51..15.53 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=25,323)

27.          

Initplan (for Limit)

28. 25.323 329.199 ↑ 1.0 1 25,323

Limit (cost=0.43..7.08 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=25,323)

29. 303.876 303.876 ↑ 2.0 1 25,323

Index Scan using idx_appt_id_ride_detail on ride_details riden (cost=0.43..13.73 rows=2 width=4) (actual time=0.012..0.012 rows=1 loops=25,323)

  • Index Cond: (ride.appt_id = appt_id)
  • Filter: (leg_no = 1)
  • Rows Removed by Filter: 0
30. 50.646 50.646 ↑ 1.0 1 25,323

Index Scan using ride_detail_id_unique on ride_additional_details ride_additional_details_1 (cost=0.43..8.45 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=25,323)

  • Index Cond: (ride_detail_id = $1)
Planning time : 10.981 ms
Execution time : 27,870.424 ms