explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nyjq

Settings
# exclusive inclusive rows x rows loops node
1. 82,787.695 217,481.637 ↑ 1.0 1 1

Hash Join (cost=5,831,347.89..16,806,120.10 rows=1 width=28) (actual time=167,450.958..217,481.637 rows=1 loops=1)

  • Hash Cond: ((orig.device_id = tb2.device_id) AND (orig.sensor_tag_id = tb2.sensor_tag_id) AND (orig.sensor_index = tb2.sensor_index) AND (orig.sensor_timestamp = (max(tb2.sensor_timestamp))))
2. 44,789.802 44,789.802 ↑ 1.0 329,243,097 1

Seq Scan on device_sensor_value orig (cost=0.00..6,036,124.68 rows=329,243,168 width=28) (actual time=0.030..44,789.802 rows=329,243,097 loops=1)

3. 0.020 89,904.140 ↑ 1.0 1 1

Hash (cost=5,831,347.87..5,831,347.87 rows=1 width=28) (actual time=89,904.140..89,904.140 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
4. 0.010 89,904.120 ↑ 1.0 1 1

GroupAggregate (cost=5,831,347.82..5,831,347.86 rows=1 width=28) (actual time=89,904.120..89,904.120 rows=1 loops=1)

  • Group Key: tb2.device_id, tb2.sensor_tag_id, tb2.sensor_index, ((floor(((tb2.sensor_timestamp / 900000))::double precision) * '900000'::double precision))
5. 0.000 89,904.110 ↓ 3.0 3 1

Sort (cost=5,831,347.82..5,831,347.82 rows=1 width=28) (actual time=89,904.109..89,904.110 rows=3 loops=1)

  • Sort Key: ((floor(((tb2.sensor_timestamp / 900000))::double precision) * '900000'::double precision))
  • Sort Method: quicksort Memory: 25kB
6. 6.265 89,905.202 ↓ 3.0 3 1

Gather (cost=1,000.00..5,831,347.81 rows=1 width=28) (actual time=54,588.516..89,905.202 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 89,898.937 89,898.937 ↑ 1.0 1 3 / 3

Parallel Seq Scan on device_sensor_value tb2 (cost=0.00..5,830,347.71 rows=1 width=28) (actual time=78,127.108..89,898.937 rows=1 loops=3)

  • Filter: ((sensor_timestamp >= '1561939200000'::bigint) AND (sensor_timestamp < '1561940100000'::bigint) AND (device_id = 192) AND (sensor_tag_id = 75) AND (sensor_index = 0)) Rows Removed by Filter: 109,747,698