explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VqbQ0 : Optimization for: plan #Pon0

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 27,554.192 707,513.421 ↓ 95.5 4,476,981 1

GroupAggregate (cost=11,239,184.08..11,241,645.34 rows=46,881 width=22) (actual time=654,354.478..707,513.421 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))
2. 205,525.737 679,959.229 ↓ 572.2 26,826,479 1

Sort (cost=11,239,184.08..11,239,301.29 rows=46,881 width=22) (actual time=654,354.291..679,959.229 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
3. 290,123.921 474,433.492 ↓ 572.2 26,826,479 1

Hash Join (cost=10,704,216.73..11,235,546.89 rows=46,881 width=22) (actual time=147,849.972..474,433.492 rows=26,826,479 loops=1)

  • Hash Cond: (cities_unrestricted_points_yesterday.edge_id = graph_edges.edge_id)
4. 17,026.714 182,902.296 ↓ 10.3 28,874,612 1

Unique (cost=10,579,795.20..11,071,332.54 rows=2,808,785 width=33) (actual time=146,439.681..182,902.296 rows=28,874,612 loops=1)

5. 81,183.859 165,875.582 ↓ 1.0 29,002,899 1

Sort (cost=10,579,795.20..10,650,014.82 rows=28,087,848 width=33) (actual time=146,439.667..165,875.582 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
6. 84,691.723 84,691.723 ↓ 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.473..84,691.723 rows=29,002,899 loops=1)

  • Filter: ((city)::text = 'almaty'::text)
  • Rows Removed by Filter: 189977209
7. 82.594 1,407.275 ↓ 1.5 94,983 1

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

  • Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3073kB
8. 1,324.681 1,324.681 ↓ 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=69.408..1,324.681 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
  • -> Bitmap Index Scan on city_graph_edges (cost=0.00..3927.48 rows=207340 width=0) (actual time=66.808..66.808 rows=200004 loops=1)" Index Cond: ((city)::text = 'almaty'::text)