explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RpQo : 123

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 35,804.166 ↓ 0.0 0 1

GroupAggregate (cost=7,070.57..7,081.49 rows=16 width=40) (actual time=35,804.166..35,804.166 rows=0 loops=1)

  • Group Key: t.trip_station_id
2. 228.000 35,804.162 ↓ 0.0 0 1

Subquery Scan on t (cost=7,070.57..7,080.65 rows=16 width=42) (actual time=35,804.162..35,804.162 rows=0 loops=1)

  • Filter: (t.point_in_staion_before <> t.point_in_staion_now)
  • Rows Removed by Filter: 2117016
3. 26,857.514 35,576.162 ↓ 132,313.5 2,117,016 1

WindowAgg (cost=7,070.57..7,080.45 rows=16 width=66) (actual time=7,595.748..35,576.162 rows=2,117,016 loops=1)

4. 3,919.819 8,718.648 ↓ 132,313.5 2,117,016 1

Sort (cost=7,070.57..7,070.61 rows=16 width=80) (actual time=7,595.650..8,718.648 rows=2,117,016 loops=1)

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

Nested Loop (cost=31.53..7,070.25 rows=16 width=80) (actual time=3,120.422..4,798.829 rows=2,117,016 loops=1)

6. 8.244 4,346.618 ↓ 9,801.0 78,408 1

Append (cost=0.00..5,403.29 rows=8 width=16) (actual time=3,120.270..4,346.618 rows=78,408 loops=1)

7. 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.004..0.005 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,234.595 4,338.369 ↓ 11,201.1 78,408 1

Bitmap Heap Scan on uzgps_track_point_2019m01 tp_1 (cost=57.54..5,403.29 rows=7 width=16) (actual time=3,120.262..4,338.369 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. 3,103.774 3,103.774 ↓ 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=3,103.774..3,103.774 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. 78.142 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)

11. 0.177 0.266 ↓ 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.122..0.266 rows=27 loops=1)

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

  • Index Cond: (trip_id = 49)