explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.008 4.961 ↓ 3.0 3 1

Limit (cost=167.20..167.97 rows=1 width=76) (actual time=2.277..4.961 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=127
2. 4.518 4.953 ↓ 3.0 3 1

Result (cost=167.20..167.97 rows=1 width=76) (actual time=2.274..4.953 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=127
3. 0.031 0.435 ↓ 3.0 3 1

Sort (cost=167.20..167.21 rows=1 width=96) (actual time=0.433..0.435 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=115
4. 0.017 0.404 ↓ 31.0 31 1

Nested Loop Left Join (cost=5.50..167.19 rows=1 width=96) (actual time=0.153..0.404 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=115
5. 0.010 0.232 ↓ 31.0 31 1

Nested Loop Left Join (cost=5.22..158.88 rows=1 width=44) (actual time=0.140..0.232 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=21
6. 0.004 0.087 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.55..16.61 rows=1 width=24) (actual time=0.066..0.087 rows=1 loops=1)

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

Index Scan using mobject_route_list_mobject_id_is_in_route_route_id_idx on bus_tablo.mobject_route_list mrl (cost=0.28..8.30 rows=1 width=16) (actual time=0.050..0.071 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
  • Index Cond: ((mrl.mobject_id = 686) AND (mrl.is_in_route = 1) AND (mrl.route_id = 68))
  • Filter: (mrl.mr_status = 'A'::bpchar)
  • Buffers: shared hit=5
8. 0.012 0.012 ↑ 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.012..0.012 rows=1 loops=1)

  • Output: rr.wkt_length, rr.id
  • Index Cond: (rr.id = 68)
  • Filter: (rr.status = 'A'::bpchar)
  • Buffers: shared hit=3
9. 0.103 0.135 ↓ 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.071..0.135 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
10. 0.032 0.032 ↓ 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.032..0.032 rows=62 loops=1)

  • Index Cond: (rrs.route_id = 68)
  • Buffers: shared hit=2
11. 0.155 0.155 ↑ 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.004..0.005 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