explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z34h : 123

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 4,512.792 ↓ 0.0 0 1

GroupAggregate (cost=0.42..7,134.88 rows=16 width=40) (actual time=4,512.792..4,512.792 rows=0 loops=1)

  • Group Key: t.trip_station_id
2. 36.531 4,512.789 ↓ 0.0 0 1

Subquery Scan on t (cost=0.42..7,134.04 rows=16 width=42) (actual time=4,512.789..4,512.789 rows=0 loops=1)

  • Filter: (t.point_in_staion_before <> t.point_in_staion_now)
  • Rows Removed by Filter: 317628
3. 4,346.260 4,476.258 ↓ 19,851.8 317,628 1

WindowAgg (cost=0.42..7,133.84 rows=16 width=58) (actual time=14.192..4,476.258 rows=317,628 loops=1)

4. 52.804 129.998 ↓ 19,851.8 317,628 1

Nested Loop (cost=0.42..7,124.04 rows=16 width=80) (actual time=14.076..129.998 rows=317,628 loops=1)

5. 6.238 6.238 ↓ 13.5 27 1

Index Scan using uzgps_routing_trip_route_station_trip_id_trip_station_id_idx on uzgps_routing_trip_route_station trs (cost=0.42..1,704.72 rows=2 width=64) (actual time=5.962..6.238 rows=27 loops=1)

  • Index Cond: (trip_id = 49)
  • Filter: (status = 'A'::bpchar)
6. 26.762 70.956 ↓ 1,470.5 11,764 27

Materialize (cost=0.00..5,419.14 rows=8 width=16) (actual time=0.301..2.628 rows=11,764 loops=27)

7. 1.263 44.194 ↓ 1,470.5 11,764 1

Append (cost=0.00..5,419.10 rows=8 width=16) (actual time=8.102..44.194 rows=11,764 loops=1)

8. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on uzgps_track_point tp (cost=0.00..0.00 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((tp_timestamp >= '2019-01-01 00:00:00'::timestamp without time zone) AND (tp_timestamp < '2019-01-02 00:00:00'::timestamp without time zone) AND (tp_unit_id = 1182) AND (tp_status = 'A'::bpchar))
9. 38.576 42.926 ↓ 1,680.6 11,764 1

Bitmap Heap Scan on uzgps_track_point_2019m01 tp_1 (cost=57.59..5,419.10 rows=7 width=16) (actual time=8.094..42.926 rows=11,764 loops=1)

  • Recheck Cond: ((tp_unit_id = 1182) AND (tp_timestamp >= '2019-01-01 00:00:00'::timestamp without time zone) AND (tp_timestamp < '2019-01-02 00:00:00'::timestamp without time zone))
  • Filter: (tp_status = 'A'::bpchar)
  • Heap Blocks: exact=11672
10. 4.350 4.350 ↓ 8.6 11,764 1

Bitmap Index Scan on uzgps_track_point_2019m01_tp_unit_id_tp_timestamp_idx (cost=0.00..57.59 rows=1,362 width=0) (actual time=4.350..4.350 rows=11,764 loops=1)

  • Index Cond: ((tp_unit_id = 1182) AND (tp_timestamp >= '2019-01-01 00:00:00'::timestamp without time zone) AND (tp_timestamp < '2019-01-02 00:00:00'::timestamp without time zone))