explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 28,255.680 583,940.236 ↓ 9.5 4,476,981 1

GroupAggregate (cost=5,096,000.96..5,120,613.54 rows=468,811 width=22) (actual time=529,897.881..583,940.236 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 hit=6579 read=2063439, temp read=638758 written=638756
2. 197,050.383 555,684.556 ↓ 57.3 26,853,817 1

Sort (cost=5,096,000.96..5,097,172.99 rows=468,811 width=22) (actual time=529,897.674..555,684.556 rows=26,853,817 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: 1282496kB
  • Buffers: shared hit=6579 read=2063439, temp read=638758 written=638756
3. 272,592.437 358,634.173 ↓ 57.3 26,853,817 1

Hash Join (cost=124,421.53..5,042,227.64 rows=468,811 width=22) (actual time=257.569..358,634.173 rows=26,853,817 loops=1)

  • Hash Cond: (cities_unrestricted_points_yesterday.edge_id = graph_edges.edge_id)
  • Buffers: shared hit=6565 read=2063439, temp read=105842 written=105840
4. 85,786.430 85,786.430 ↓ 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.391..85,786.430 rows=29,002,899 loops=1)

  • Filter: ((city)::text = 'almaty'::text)
  • Rows Removed by Filter: 189977209
  • Buffers: shared hit=66 read=2063439
5. 53.812 255.306 ↓ 1.5 94,983 1

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

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3073kB
  • Buffers: shared hit=6499, temp written=162
6. 168.973 201.494 ↓ 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=34.144..201.494 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 hit=6499
7. 32.521 32.521 ↑ 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=32.521..32.521 rows=200,004 loops=1)

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