explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 39Ez

Settings
# exclusive inclusive rows x rows loops node
1. 0.147 70,962.850 ↓ 66.0 66 1

Sort (cost=8,293,395.58..8,293,395.58 rows=1 width=78) (actual time=70,962.845..70,962.850 rows=66 loops=1)

  • Sort Key: t.id
  • Sort Method: quicksort Memory: 82kB
2. 0.369 70,962.703 ↓ 66.0 66 1

Nested Loop (cost=185,785.33..8,293,395.57 rows=1 width=78) (actual time=69,920.385..70,962.703 rows=66 loops=1)

3. 0.073 70,962.136 ↓ 66.0 66 1

Nested Loop (cost=185,784.91..8,293,388.14 rows=1 width=59) (actual time=69,920.340..70,962.136 rows=66 loops=1)

4. 0.056 70,961.931 ↓ 66.0 66 1

Nested Loop (cost=185,784.48..8,293,380.75 rows=1 width=40) (actual time=69,920.334..70,961.931 rows=66 loops=1)

  • Join Filter: (st.trip_id = stend.trip_id)
5. 6.871 70,959.763 ↓ 13.2 66 1

Hash Join (cost=185,783.91..8,281,767.39 rows=5 width=36) (actual time=69,920.217..70,959.763 rows=66 loops=1)

  • Hash Cond: (t.route_id = r.id)
6. 620.616 70,951.977 ↓ 3.7 16,107 1

Nested Loop (cost=185,280.80..8,281,247.68 rows=4,412 width=40) (actual time=1,481.882..70,951.977 rows=16,107 loops=1)

7. 53,936.742 55,408.018 ↓ 12.9 648,841 1

Bitmap Heap Scan on stop_times st (cost=185,280.37..2,467,791.42 rows=50,160 width=16) (actual time=1,481.780..55,408.018 rows=648,841 loops=1)

  • Recheck Cond: (stop_sequence = 1)
  • Rows Removed by Index Recheck: 193481923
  • Filter: ((((date_part('epoch'::text, (now())::timestamp without time zone))::integer % 86400) < (departure_time % 86400)) AND ((((date_part('epoch'::text, (now())::timestamp without time zone))::integer % 86400) + 3600 (...)
  • Rows Removed by Filter: 9401105
  • Heap Blocks: exact=41571 lossy=1737060
8. 1,471.276 1,471.276 ↓ 1.0 10,049,946 1

Bitmap Index Scan on stop_times_idx_stop_sequence (cost=0.00..185,267.83 rows=10,031,901 width=0) (actual time=1,471.276..1,471.276 rows=10,049,946 loops=1)

  • Index Cond: (stop_sequence = 1)
9. 5,544.827 14,923.343 ↓ 0.0 0 648,841

Index Scan using trips_pk on trips t (cost=0.43..115.89 rows=1 width=24) (actual time=0.023..0.023 rows=0 loops=648,841)

  • Index Cond: (id = st.trip_id)
  • Filter: (('2019-08-08'::date = ANY (validity_date)) AND (SubPlan 2))
  • Rows Removed by Filter: 1
10.          

SubPlan (forIndex Scan)

11. 138.107 9,378.516 ↓ 0.0 0 111,649

Nested Loop (cost=1.00..322.83 rows=3 width=0) (actual time=0.084..0.084 rows=0 loops=111,649)

12. 3,461.119 3,461.119 ↑ 1.7 17 111,649

Index Scan using stop_times_idx_trip_id on stop_times st1 (cost=0.57..77.64 rows=29 width=4) (actual time=0.027..0.031 rows=17 loops=111,649)

  • Index Cond: (trip_id = t.id)
13. 5,779.290 5,779.290 ↓ 0.0 0 1,926,430

Index Scan using stops_pk on stops s1 (cost=0.42..8.45 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1,926,430)

  • Index Cond: (id = st1.stop_id)
  • Filter: ((region_entity_type)::text = 'muni'::text)
  • Rows Removed by Filter: 1
14. 0.168 0.915 ↓ 6.5 1,059 1

Hash (cost=501.07..501.07 rows=163 width=4) (actual time=0.915..0.915 rows=1,059 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 54kB
15. 0.210 0.747 ↓ 6.5 1,059 1

Nested Loop (cost=0.42..501.07 rows=163 width=4) (actual time=0.172..0.747 rows=1,059 loops=1)

16. 0.173 0.173 ↑ 1.0 1 1

Seq Scan on pkg p (cost=0.00..23.15 rows=1 width=8) (actual time=0.155..0.173 rows=1 loops=1)

  • Filter: (dataimport_id = 1437)
  • Rows Removed by Filter: 891
17. 0.364 0.364 ↓ 3.7 1,059 1

Index Scan using routes_idx_pkg on routes r (cost=0.42..475.05 rows=287 width=12) (actual time=0.013..0.364 rows=1,059 loops=1)

  • Index Cond: (pkg_id = p.id)
18. 0.402 2.112 ↑ 1.0 1 66

Index Scan using stop_times_idx_trip_id on stop_times stend (cost=0.57..2,322.66 rows=1 width=16) (actual time=0.032..0.032 rows=1 loops=66)

  • Index Cond: (trip_id = t.id)
  • Filter: ((SubPlan 1) = stop_sequence)
  • Rows Removed by Filter: 4
19.          

SubPlan (forIndex Scan)

20. 0.684 1.710 ↑ 1.0 1 342

Aggregate (cost=77.71..77.72 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=342)

21. 1.026 1.026 ↑ 4.1 7 342

Index Scan using stop_times_idx_trip_id on stop_times (cost=0.57..77.64 rows=29 width=4) (actual time=0.002..0.003 rows=7 loops=342)

  • Index Cond: (trip_id = t.id)
22. 0.132 0.132 ↑ 1.0 1 66

Index Scan using stops_pk on stops s (cost=0.42..7.38 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=66)

  • Index Cond: (id = st.stop_id)
23. 0.198 0.198 ↑ 1.0 1 66

Index Scan using stops_pk on stops send (cost=0.42..7.36 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=66)

  • Index Cond: (id = stend.stop_id)
Planning time : 157.826 ms
Execution time : 70,963.115 ms