explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z5hS : 123

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 32,085.230 ↓ 0.0 0 1

GroupAggregate (cost=1,744.27..1,755.19 rows=16 width=40) (actual time=32,085.230..32,085.230 rows=0 loops=1)

  • Group Key: t.trip_station_id
2. 217.364 32,085.227 ↓ 0.0 0 1

Subquery Scan on t (cost=1,744.27..1,754.35 rows=16 width=42) (actual time=32,085.227..32,085.227 rows=0 loops=1)

  • Filter: (t.point_in_staion_before <> t.point_in_staion_now)
  • Rows Removed by Filter: 2117016
3. 27,440.091 31,867.863 ↓ 132,313.5 2,117,016 1

WindowAgg (cost=1,744.27..1,754.15 rows=16 width=66) (actual time=3,299.763..31,867.863 rows=2,117,016 loops=1)

4. 3,874.302 4,427.772 ↓ 132,313.5 2,117,016 1

Sort (cost=1,744.27..1,744.31 rows=16 width=80) (actual time=3,299.655..4,427.772 rows=2,117,016 loops=1)

  • Sort Key: trs.trip_station_id, tp.tp_timestamp
  • Sort Method: external merge Disk: 120064kB
5. 332.685 553.470 ↓ 132,313.5 2,117,016 1

Nested Loop (cost=31.53..1,743.95 rows=16 width=80) (actual time=0.076..553.470 rows=2,117,016 loops=1)

6. 5.199 142.377 ↓ 9,801.0 78,408 1

Append (cost=0.00..76.99 rows=8 width=16) (actual time=0.039..142.377 rows=78,408 loops=1)

7. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: ((tp_timestamp >= '2019-01-01 00:00:00'::timestamp without time zone) AND (tp_timestamp < '2019-01-08 00:00:00'::timestamp without time zone) AND (tp_unit_id = 1182) AND (tp_status = 'A'::bpchar))
8. 137.175 137.175 ↓ 11,201.1 78,408 1

Index Scan using uzgps_track_point_2019m01_tp_unit_id_tp_timestamp_tp_status_idx on uzgps_track_point_2019m01 tp_1 (cost=0.56..76.99 rows=7 width=16) (actual time=0.036..137.175 rows=78,408 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-08 00:00:00'::timestamp without time zone) AND (tp_status = 'A'::bpchar))
9. 78.336 78.408 ↓ 13.5 27 78,408

Materialize (cost=31.53..1,666.76 rows=2 width=64) (actual time=0.000..0.001 rows=27 loops=78,408)

10. 0.052 0.072 ↓ 13.5 27 1

Bitmap Heap Scan on uzgps_routing_trip_route_station trs (cost=31.53..1,666.75 rows=2 width=64) (actual time=0.031..0.072 rows=27 loops=1)

  • Recheck Cond: (trip_id = 49)
  • Filter: (status = 'A'::bpchar)
  • Heap Blocks: exact=10
11. 0.020 0.020 ↑ 15.4 27 1

Bitmap Index Scan on uzgps_routing_trip_route_station_trip_id_idx (cost=0.00..31.53 rows=415 width=0) (actual time=0.020..0.020 rows=27 loops=1)

  • Index Cond: (trip_id = 49)