explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 27,606.435 752,967.780 ↓ 95.5 4,476,981 1

GroupAggregate (cost=11,239,184.08..11,241,645.34 rows=46,881 width=22) (actual time=699,175.590..752,967.780 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=1 read=2070003, temp read=804966 written=804964
2. 214,704.173 725,361.345 ↓ 572.2 26,826,479 1

Sort (cost=11,239,184.08..11,239,301.29 rows=46,881 width=22) (actual time=699,175.282..725,361.345 rows=26,826,479 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: 1281336kB
  • Buffers: shared hit=1 read=2070003, temp read=804966 written=804964
3. 305,876.508 510,657.172 ↓ 572.2 26,826,479 1

Hash Join (cost=10,704,216.73..11,235,546.89 rows=46,881 width=22) (actual time=163,575.346..510,657.172 rows=26,826,479 loops=1)

  • Hash Cond: (cities_unrestricted_points_yesterday.edge_id = graph_edges.edge_id)
  • Buffers: shared hit=1 read=2070003, temp read=279037 written=279035
4. 18,875.729 203,230.633 ↓ 10.3 28,874,612 1

Unique (cost=10,579,795.20..11,071,332.54 rows=2,808,785 width=33) (actual time=162,023.469..203,230.633 rows=28,874,612 loops=1)

  • Buffers: shared read=2063505, temp read=173650 written=173650
5. 89,087.745 184,354.904 ↓ 1.0 29,002,899 1

Sort (cost=10,579,795.20..10,650,014.82 rows=28,087,848 width=33) (actual time=162,023.462..184,354.904 rows=29,002,899 loops=1)

  • Sort Key: cities_unrestricted_points_yesterday."timestamp", cities_unrestricted_points_yesterday.edge_id, cities_unrestricted_points_yesterday.device_id, cities_unrestricted_points_yesterday.speed, cities_unrestricted_points_yesterday.ppc
  • Sort Method: external merge Disk: 1389184kB
  • Buffers: shared read=2063505, temp read=173650 written=173650
6. 95,267.159 95,267.159 ↓ 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=33) (actual time=0.464..95,267.159 rows=29,002,899 loops=1)

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

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

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3073kB
  • Buffers: shared hit=1 read=6498, temp written=162
8. 1,382.656 1,461.678 ↓ 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=81.065..1,461.678 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=1 read=6498
9. 79.022 79.022 ↑ 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=79.022..79.022 rows=200,004 loops=1)

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