explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YUjg : 234

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 37,211.194 ↓ 0.0 0 1

GroupAggregate (cost=7,070.58..7,081.50 rows=16 width=40) (actual time=37,211.194..37,211.194 rows=0 loops=1)

  • Group Key: t.trip_station_id
2. 257.624 37,211.176 ↓ 0.0 0 1

Subquery Scan on t (cost=7,070.58..7,080.66 rows=16 width=42) (actual time=37,211.176..37,211.176 rows=0 loops=1)

  • Filter: (t.point_in_staion_before <> t.point_in_staion_now)
  • Rows Removed by Filter: 2117016
3. 29,805.431 36,953.552 ↓ 132,313.5 2,117,016 1

WindowAgg (cost=7,070.58..7,080.46 rows=16 width=58) (actual time=5,900.809..36,953.552 rows=2,117,016 loops=1)

4. 5,044.033 7,148.121 ↓ 132,313.5 2,117,016 1

Sort (cost=7,070.58..7,070.62 rows=16 width=80) (actual time=5,900.603..7,148.121 rows=2,117,016 loops=1)

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

Nested Loop (cost=31.53..7,070.26 rows=16 width=80) (actual time=64.533..2,104.088 rows=2,117,016 loops=1)

6. 10.537 1,531.012 ↓ 9,801.0 78,408 1

Append (cost=0.00..5,403.31 rows=8 width=16) (actual time=64.411..1,531.012 rows=78,408 loops=1)

7. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on uzgps_track_point tp (cost=0.00..0.00 rows=1 width=16) (actual time=0.004..0.004 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. 1,472.571 1,520.471 ↓ 11,201.1 78,408 1

Bitmap Heap Scan on uzgps_track_point_2019m01 tp_1 (cost=57.54..5,403.31 rows=7 width=16) (actual time=64.406..1,520.471 rows=78,408 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-08 00:00:00'::timestamp without time zone))
  • Rows Removed by Index Recheck: 1345956
  • Filter: (tp_status = 'A'::bpchar)
  • Heap Blocks: exact=33369 lossy=44869
9. 47.900 47.900 ↓ 57.7 78,408 1

Bitmap Index Scan on uzgps_track_point_2019m01_tp_unit_id_tp_timestamp_idx (cost=0.00..57.54 rows=1,358 width=0) (actual time=47.900..47.900 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))
10. 156.601 156.816 ↓ 13.5 27 78,408

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

11. 0.128 0.215 ↓ 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.110..0.215 rows=27 loops=1)

  • Recheck Cond: (trip_id = 49)
  • Filter: (status = 'A'::bpchar)
  • Heap Blocks: exact=10
12. 0.087 0.087 ↑ 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.087..0.087 rows=27 loops=1)

  • Index Cond: (trip_id = 49)