explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jQr3 : Optimization for: Optimization for: Optimization for: plan #Pon0; plan #VqbQ0; plan #kZV9

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 27,924.619 694,369.829 ↓ 95.5 4,476,981 1

GroupAggregate (cost=9,946,702.34..9,949,163.60 rows=46,881 width=22) (actual time=641,267.453..694,369.829 rows=4,476,981 loops=1)

  • Group Key: (date_trunc('hour'::text, to_timestamp((cities_unrestricted_points_yesterday."timestamp")::double precision))), cities_unrestricted_points_yesterday.edge_id, ((cities_unrestricted_points_yesterday.speed = 0)), (div((cities_unrestricted_points_yesterday.speed)::numeric, '5'::numeric))
  • Buffers: shared read=2070004, temp read=678022 written=678020
2. 188,226.040 666,445.210 ↓ 494.0 23,159,053 1

Sort (cost=9,946,702.34..9,946,819.55 rows=46,881 width=22) (actual time=641,267.249..666,445.210 rows=23,159,053 loops=1)

  • Sort Key: (date_trunc('hour'::text, to_timestamp((cities_unrestricted_points_yesterday."timestamp")::double precision))), cities_unrestricted_points_yesterday.edge_id, ((cities_unrestricted_points_yesterday.speed = 0)), (div((cities_unrestricted_points_yesterday.speed)::numeric, '5'::numeric))
  • Sort Method: external merge Disk: 1114152kB
  • Buffers: shared read=2070004, temp read=678022 written=678020
3. 280,297.044 478,219.170 ↓ 494.0 23,159,053 1

Hash Join (cost=9,552,174.23..9,943,065.15 rows=46,881 width=22) (actual time=164,880.910..478,219.170 rows=23,159,053 loops=1)

  • Hash Cond: (cities_unrestricted_points_yesterday.edge_id = graph_edges.edge_id)
  • Buffers: shared read=2070004, temp read=228152 written=228150
4. 13,486.163 196,707.830 ↓ 8.8 24,668,081 1

Unique (cost=9,427,752.70..9,778,850.80 rows=2,808,785 width=22) (actual time=163,664.434..196,707.830 rows=24,668,081 loops=1)

  • Buffers: shared read=2063505, temp read=148843 written=148843
5. 88,442.630 183,221.667 ↓ 1.0 29,002,899 1

Sort (cost=9,427,752.70..9,497,972.32 rows=28,087,848 width=22) (actual time=163,664.431..183,221.667 rows=29,002,899 loops=1)

  • Sort Key: cities_unrestricted_points_yesterday."timestamp", cities_unrestricted_points_yesterday.edge_id, cities_unrestricted_points_yesterday.speed, cities_unrestricted_points_yesterday.device_id
  • Sort Method: external merge Disk: 1190728kB
  • Buffers: shared read=2063505, temp read=148843 written=148843
6. 94,779.037 94,779.037 ↓ 1.0 29,002,899 1

Seq Scan on cities_unrestricted_points_yesterday (cost=0.00..4,800,756.40 rows=28,087,848 width=22) (actual time=0.594..94,779.037 rows=29,002,899 loops=1)

  • Filter: ((city)::text = 'almaty'::text)
  • Rows Removed by Filter: 189977209
  • Buffers: shared read=2063505
7. 83.133 1,214.296 ↓ 1.5 94,983 1

Hash (cost=123,607.38..123,607.38 rows=65,132 width=8) (actual time=1,214.296..1,214.296 rows=94,983 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3073kB
  • Buffers: shared read=6499, temp written=162
8. 1,038.011 1,131.163 ↓ 1.5 94,983 1

Bitmap Heap Scan on graph_edges (cost=3,943.76..123,607.38 rows=65,132 width=8) (actual time=97.527..1,131.163 rows=94,983 loops=1)

  • Recheck Cond: ((city)::text = 'almaty'::text)
  • Filter: ((road_class > 1) AND (road_class < 8))
  • Rows Removed by Filter: 105021
  • Heap Blocks: exact=5950
  • Buffers: shared read=6499
9. 93.152 93.152 ↑ 1.0 200,004 1

Bitmap Index Scan on city_graph_edges (cost=0.00..3,927.48 rows=207,340 width=0) (actual time=93.152..93.152 rows=200,004 loops=1)

  • Index Cond: ((city)::text = 'almaty'::text)" Buffers: shared read=549