explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cWtm

Settings
# exclusive inclusive rows x rows loops node
1. 1.243 852.130 ↑ 10.4 17 1

GroupAggregate (cost=265,106.22..265,111.06 rows=176 width=60) (actual time=850.746..852.130 rows=17 loops=1)

  • Output: t.fleet, t.model, (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), (sum((count(*))) / '144'::numeric), t.fleet, t.model
  • Group Key: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), t.fleet, t.model
2. 2.534 850.887 ↓ 19.8 3,484 1

Sort (cost=265,106.22..265,106.66 rows=176 width=26) (actual time=850.617..850.887 rows=3,484 loops=1)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), t.fleet, t.model, (count(*))
  • Sort Key: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), t.fleet, t.model
  • Sort Method: quicksort Memory: 369kB
3. 2.148 848.353 ↓ 19.8 3,484 1

Hash Join (cost=130,297.19..265,099.66 rows=176 width=26) (actual time=580.487..848.353 rows=3,484 loops=1)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), t.fleet, t.model, (count(*))
  • Inner Unique: true
  • Hash Cond: (_hyper_1_187_chunk.tags_id = t.id)
4. 10.571 777.515 ↑ 82.5 3,484 1

Finalize GroupAggregate (cost=103,614.48..234,788.79 rows=287,379 width=28) (actual time=511.777..777.515 rows=3,484 loops=1)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_187_chunk."time")), _hyper_1_187_chunk.tags_id, count(*)
  • Group Key: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_187_chunk."time")), _hyper_1_187_chunk.tags_id
  • Filter: (avg(_hyper_1_187_chunk.status) < '1'::double precision)
  • Rows Removed by Filter: 21541
5. 0.000 766.944 ↑ 26.3 31,706 1

Gather Merge (cost=103,614.48..209,990.79 rows=834,324 width=60) (actual time=511.732..766.944 rows=31,706 loops=1)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_187_chunk."time")), _hyper_1_187_chunk.tags_id, (PARTIAL count(*)), (PARTIAL avg(_hyper_1_187_chunk.status))
  • Workers Planned: 3
  • Workers Launched: 3
6. 301.980 2,531.548 ↑ 35.1 7,926 4

Partial GroupAggregate (cost=102,614.44..110,957.68 rows=278,108 width=60) (actual time=501.218..632.887 rows=7,926 loops=4)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_187_chunk."time")), _hyper_1_187_chunk.tags_id, PARTIAL count(*), PARTIAL avg(_hyper_1_187_chunk.status)
  • Group Key: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_187_chunk."time")), _hyper_1_187_chunk.tags_id
  • Worker 0: actual time=499.024..631.183 rows=8060 loops=1
  • Worker 1: actual time=498.918..633.339 rows=7888 loops=1
  • Worker 2: actual time=496.755..628.818 rows=7731 loops=1
7. 998.036 2,229.568 ↑ 1.3 216,260 4

Sort (cost=102,614.44..103,309.71 rows=278,108 width=28) (actual time=501.192..557.392 rows=216,260 loops=4)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_187_chunk."time")), _hyper_1_187_chunk.tags_id, _hyper_1_187_chunk.status
  • Sort Key: (time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_187_chunk."time")), _hyper_1_187_chunk.tags_id
  • Sort Method: external merge Disk: 8824kB
  • Worker 0: Sort Method: external merge Disk: 8896kB
  • Worker 1: Sort Method: external merge Disk: 8888kB
  • Worker 2: Sort Method: external merge Disk: 8752kB
  • Worker 0: actual time=499.002..555.414 rows=217615 loops=1
  • Worker 1: actual time=498.903..557.737 rows=217384 loops=1
  • Worker 2: actual time=496.711..554.478 rows=214081 loops=1
8. 271.780 1,231.532 ↑ 1.3 216,260 4

Result (cost=0.00..73,567.03 rows=278,108 width=28) (actual time=0.177..307.883 rows=216,260 loops=4)

  • Output: time_bucket('24:00:00'::interval, _hyper_1_187_chunk."time"), time_bucket('00:10:00'::interval, _hyper_1_187_chunk."time"), _hyper_1_187_chunk.tags_id, _hyper_1_187_chunk.status
  • Worker 0: actual time=0.070..303.734 rows=217615 loops=1
  • Worker 1: actual time=0.245..307.347 rows=217384 loops=1
  • Worker 2: actual time=0.377..306.722 rows=214081 loops=1
9. 103.080 959.752 ↑ 1.3 216,260 4

Parallel Append (cost=0.00..69,395.41 rows=278,108 width=20) (actual time=0.172..239.938 rows=216,260 loops=4)

  • Worker 0: actual time=0.067..235.860 rows=217615 loops=1
  • Worker 1: actual time=0.232..240.222 rows=217384 loops=1
  • Worker 2: actual time=0.375..238.301 rows=214081 loops=1
10. 486.884 486.884 ↑ 1.3 122,970 4

Parallel Seq Scan on _timescaledb_internal._hyper_1_187_chunk (cost=0.00..34,005.30 rows=157,848 width=20) (actual time=0.180..121.721 rows=122,970 loops=4)

  • Output: _hyper_1_187_chunk."time", _hyper_1_187_chunk.tags_id, _hyper_1_187_chunk.status
  • Filter: ((_hyper_1_187_chunk."time" >= '2019-02-16 10:16:22.646325+00'::timestamp with time zone) AND (_hyper_1_187_chunk."time" < '2019-02-16 14:16:22.646325+00'::timestamp with time zone))
  • Rows Removed by Filter: 525617
  • Worker 0: actual time=0.044..30.641 rows=30715 loops=1
  • Worker 1: actual time=0.231..214.889 rows=217384 loops=1
  • Worker 2: actual time=0.373..212.255 rows=214081 loops=1
11. 369.788 369.788 ↓ 1.6 186,579 2

Parallel Seq Scan on _timescaledb_internal._hyper_1_186_chunk (cost=0.00..33,999.57 rows=120,260 width=20) (actual time=0.039..184.894 rows=186,579 loops=2)

  • Output: _hyper_1_186_chunk."time", _hyper_1_186_chunk.tags_id, _hyper_1_186_chunk.status
  • Filter: ((_hyper_1_186_chunk."time" >= '2019-02-16 10:16:22.646325+00'::timestamp with time zone) AND (_hyper_1_186_chunk."time" < '2019-02-16 14:16:22.646325+00'::timestamp with time zone))
  • Rows Removed by Filter: 1110416
  • Worker 0: actual time=0.066..179.721 rows=186900 loops=1
12. 0.085 68.690 ↑ 1.0 1,045 1

Hash (cost=26,669.20..26,669.20 rows=1,081 width=14) (actual time=68.690..68.690 rows=1,045 loops=1)

  • Output: t.fleet, t.model, t.id
  • Buckets: 2048 Batches: 1 Memory Usage: 66kB
13. 7.582 68.605 ↑ 1.0 1,045 1

Gather (cost=1,000.00..26,669.20 rows=1,081 width=14) (actual time=0.458..68.605 rows=1,045 loops=1)

  • Output: t.fleet, t.model, t.id
  • Workers Planned: 3
  • Workers Launched: 3
14. 61.023 61.023 ↑ 1.3 261 4

Parallel Seq Scan on public.tags t (cost=0.00..25,561.10 rows=349 width=14) (actual time=44.127..61.023 rows=261 loops=4)

  • Output: t.fleet, t.model, t.id
  • Filter: (t.name IS NOT NULL)
  • Rows Removed by Filter: 440411
  • Worker 0: actual time=58.834..58.834 rows=0 loops=1
  • Worker 1: actual time=58.831..58.831 rows=0 loops=1
  • Worker 2: actual time=58.831..58.831 rows=0 loops=1