explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SD9y : Optimization for: Optimization for: plan #iedG; plan #N2Gf

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.007 3.787 ↓ 3.0 3 1

Limit (cost=171.78..172.55 rows=1 width=76) (actual time=2.238..3.787 rows=3 loops=1)

  • Output: mrl.mobject_id, mrl.route_id, rs.id, rrs.id, (CASE WHEN (bus_tablo.distance_between_wkt(rs.wkt, 'POINT(69.32798767089844 41.314884185791016)'::text) < rs.shape_radius) THEN 1 ELSE 0 END), (bus_tablo.distance_between_wkt(rs.wkt, 'POINT(69.32798767 (...)
  • Buffers: shared hit=143
2. 2.753 3.780 ↓ 3.0 3 1

Result (cost=171.78..172.55 rows=1 width=76) (actual time=2.236..3.780 rows=3 loops=1)

  • Output: mrl.mobject_id, mrl.route_id, rs.id, rrs.id, CASE WHEN (bus_tablo.distance_between_wkt(rs.wkt, 'POINT(69.32798767089844 41.314884185791016)'::text) < rs.shape_radius) THEN 1 ELSE 0 END, bus_tablo.distance_between_wkt(rs.wkt, 'POINT(69.32798 (...)
  • Buffers: shared hit=143
3. 0.037 1.027 ↓ 3.0 3 1

Sort (cost=171.78..171.79 rows=1 width=96) (actual time=1.026..1.027 rows=3 loops=1)

  • Output: mrl.mobject_id, mrl.route_id, rs.id, rrs.id, '0.927902912082601'::double precision, 1, rrs.priority, rr.wkt_length, rs.wkt, rs.shape_radius
  • Sort Key: rrs.priority
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=131
4. 0.015 0.990 ↓ 31.0 31 1

Nested Loop Left Join (cost=14.08..171.77 rows=1 width=96) (actual time=0.604..0.990 rows=31 loops=1)

  • Output: mrl.mobject_id, mrl.route_id, rs.id, rrs.id, '0.927902912082601'::double precision, 1, rrs.priority, rr.wkt_length, rs.wkt, rs.shape_radius
  • Buffers: shared hit=131
5. 0.009 0.789 ↓ 31.0 31 1

Nested Loop Left Join (cost=13.80..163.46 rows=1 width=44) (actual time=0.590..0.789 rows=31 loops=1)

  • Output: mrl.mobject_id, mrl.route_id, rr.wkt_length, rrs.id, rrs.priority, rrs.station_id
  • Join Filter: (mrl.route_id = rrs.route_id)
  • Buffers: shared hit=37
6. 0.004 0.701 ↑ 1.0 1 1

Nested Loop Left Join (cost=9.14..21.19 rows=1 width=24) (actual time=0.546..0.701 rows=1 loops=1)

  • Output: mrl.mobject_id, mrl.route_id, rr.wkt_length
  • Join Filter: (mrl.route_id = rr.id)
  • Buffers: shared hit=24
7. 0.337 0.680 ↑ 1.0 1 1

Bitmap Heap Scan on bus_tablo.mobject_route_list mrl (cost=8.86..12.88 rows=1 width=16) (actual time=0.526..0.680 rows=1 loops=1)

  • Output: mrl.id, mrl.route_id, mrl.mobject_id, mrl.mr_status, mrl.reg_date, mrl.mod_date, mrl.exp_date, mrl.is_in_route, mrl.percent_point_to_route, mrl.coor_lat, mrl.coor_lng, mrl.route_length, mrl.speed
  • Recheck Cond: ((mrl.is_in_route = 1) AND (mrl.mr_status = 'A'::bpchar))
  • Filter: ((mrl.route_id = 68) AND (mrl.mobject_id = 686))
  • Rows Removed by Filter: 151
  • Heap Blocks: exact=12
  • Buffers: shared hit=21
8. 0.005 0.343 ↓ 0.0 0 1

BitmapAnd (cost=8.86..8.86 rows=1 width=0) (actual time=0.343..0.343 rows=0 loops=1)

  • Buffers: shared hit=9
9. 0.172 0.172 ↓ 158.2 633 1

Bitmap Index Scan on mobject_route_list_is_in_route_idx (cost=0.00..4.31 rows=4 width=0) (actual time=0.172..0.172 rows=633 loops=1)

  • Index Cond: (mrl.is_in_route = 1)
  • Buffers: shared hit=4
10. 0.166 0.166 ↓ 196.2 785 1

Bitmap Index Scan on mobject_route_list_mr_status_idx (cost=0.00..4.31 rows=4 width=0) (actual time=0.166..0.166 rows=785 loops=1)

  • Index Cond: (mrl.mr_status = 'A'::bpchar)
  • Buffers: shared hit=5
11. 0.017 0.017 ↑ 1.0 1 1

Index Scan using uzgps_routing_route_id_idx on public.uzgps_routing_route rr (cost=0.28..8.29 rows=1 width=16) (actual time=0.016..0.017 rows=1 loops=1)

  • Output: rr.wkt_length, rr.id
  • Index Cond: (rr.id = 68)
  • Filter: (rr.status = 'A'::bpchar)
  • Buffers: shared hit=3
12. 0.060 0.079 ↓ 31.0 31 1

Bitmap Heap Scan on public.uzgps_routing_route_station rrs (cost=4.67..142.26 rows=1 width=28) (actual time=0.041..0.079 rows=31 loops=1)

  • Output: rrs.id, rrs.route_id, rrs.station_id, rrs.priority, rrs.time_enter, rrs.time_exit, rrs.time_parking, rrs.time_travel, rrs.time_deviation, rrs.distance, rrs.status, rrs.reg_date, rrs.mod_date, rrs.exp_date, rrs.wkt, r (...)
  • Recheck Cond: (rrs.route_id = 68)
  • Filter: (rrs.status = 'A'::bpchar)
  • Rows Removed by Filter: 31
  • Heap Blocks: exact=11
  • Buffers: shared hit=13
13. 0.019 0.019 ↓ 1.2 62 1

Bitmap Index Scan on uzgps_routing_route_station_route_id_idx (cost=0.00..4.67 rows=51 width=0) (actual time=0.019..0.019 rows=62 loops=1)

  • Index Cond: (rrs.route_id = 68)
  • Buffers: shared hit=2
14. 0.186 0.186 ↑ 1.0 1 31

Index Scan using uzgps_routing_station_pkey on public.uzgps_routing_station rs (cost=0.28..8.30 rows=1 width=48) (actual time=0.005..0.006 rows=1 loops=31)

  • Output: rs.id, rs.contract_id, rs.name, rs.use_name, rs.shape_type, rs.shape_radius, rs.shape_area, rs.shape_perimeter, rs.shape_color, rs.font_color, rs.font_size, rs.status, rs.reg_date, rs.mod_date, rs.exp_date, rs.wkt, rs.wkt_ (...)
  • Index Cond: (rs.id = rrs.station_id)
  • Filter: (rs.status = 'A'::bpchar)
  • Buffers: shared hit=94