explain.depesz.com

PostgreSQL's explain analyze made readable

Result: msx7

Settings
# exclusive inclusive rows x rows loops node
1. 1.175 1,637.541 ↓ 17.0 17 1

GroupAggregate (cost=8,179.19..8,179.22 rows=1 width=60) (actual time=1,636.265..1,637.541 rows=17 loops=1)

  • Output: t.fleet, t.model, (time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time")), (sum((count(*))) / '144'::numeric), t.fleet, t.model
  • Group Key: (time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time")), t.fleet, t.model
2. 2.374 1,636.366 ↓ 3,484.0 3,484 1

Sort (cost=8,179.19..8,179.19 rows=1 width=26) (actual time=1,636.138..1,636.366 rows=3,484 loops=1)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time")), t.fleet, t.model, (count(*))
  • Sort Key: (time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time")), t.fleet, t.model
  • Sort Method: quicksort Memory: 369kB
3. 1.975 1,633.992 ↓ 3,484.0 3,484 1

Nested Loop (cost=7,918.38..8,179.18 rows=1 width=26) (actual time=1,070.002..1,633.992 rows=3,484 loops=1)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time")), t.fleet, t.model, (count(*))
  • Inner Unique: true
4. 299.265 1,625.049 ↓ 36.7 3,484 1

GroupAggregate (cost=7,917.95..7,926.95 rows=95 width=28) (actual time=1,069.961..1,625.049 rows=3,484 loops=1)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_186_chunk."time")), _hyper_1_186_chunk.tags_id, count(*)
  • Group Key: (time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_186_chunk."time")), _hyper_1_186_chunk.tags_id
  • Filter: (avg(_hyper_1_186_chunk.status) < '1'::double precision)
  • Rows Removed by Filter: 21541
5. 978.018 1,325.784 ↓ 3,045.9 865,039 1

Sort (cost=7,917.95..7,918.66 rows=284 width=28) (actual time=1,069.925..1,325.784 rows=865,039 loops=1)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_186_chunk."time")), _hyper_1_186_chunk.tags_id, _hyper_1_186_chunk.status
  • Sort Key: (time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_186_chunk."time")), _hyper_1_186_chunk.tags_id
  • Sort Method: external merge Disk: 35344kB
6. 46.648 347.766 ↓ 3,045.9 865,039 1

Gather (cost=1,003.37..7,906.38 rows=284 width=28) (actual time=0.868..347.766 rows=865,039 loops=1)

  • Output: (time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time")), (time_bucket('00:10:00'::interval, _hyper_1_186_chunk."time")), _hyper_1_186_chunk.tags_id, _hyper_1_186_chunk.status
  • Workers Planned: 2
  • Workers Launched: 2
7. 92.697 301.118 ↓ 1.0 288,346 3

Result (cost=3.37..6,877.98 rows=277,666 width=28) (actual time=0.207..301.118 rows=288,346 loops=3)

  • Output: time_bucket('24:00:00'::interval, _hyper_1_186_chunk."time"), time_bucket('00:10:00'::interval, _hyper_1_186_chunk."time"), _hyper_1_186_chunk.tags_id, _hyper_1_186_chunk.status
  • Worker 0: actual time=0.237..397.603 rows=335593 loops=1
  • Worker 1: actual time=0.226..378.955 rows=400556 loops=1
8. 0.000 208.421 ↓ 1.0 288,346 3

Parallel Append (cost=3.37..2,712.99 rows=277,666 width=20) (actual time=0.205..208.421 rows=288,346 loops=3)

  • Worker 0: actual time=0.235..289.455 rows=335593 loops=1
  • Worker 1: actual time=0.224..250.193 rows=400556 loops=1
9. 90.389 92.851 ↑ 1.6 124,386 3

Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_1_186_chunk (cost=3.38..663.33 rows=196,000 width=20) (actual time=0.157..92.851 rows=124,386 loops=3)

  • 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: 209281
  • Worker 0: actual time=0.234..250.087 rows=335593 loops=1
  • Worker 1: actual time=0.195..20.648 rows=27561 loops=1
10. 2.462 2.462 ↓ 1.7 334 3

Parallel Seq Scan on _timescaledb_internal.compress_hyper_6_1362_chunk (cost=0.00..663.33 rows=196 width=366) (actual time=0.025..2.462 rows=334 loops=3)

  • Output: compress_hyper_6_1362_chunk._ts_meta_count, compress_hyper_6_1362_chunk."time", compress_hyper_6_1362_chunk.tags_id, compress_hyper_6_1362_chunk.status
  • Filter: ((_timescaledb_internal.segment_meta_get_max(compress_hyper_6_1362_chunk._ts_meta_min_max_1, NULL::timestamp with time zone) >= '2019-02-16 10:16:22.646325+00'::timestamp with time zone) AND (_timescaledb_internal.segment_meta_get_min(compress_hyper_6_1362_chunk._ts_meta_min_max_1, NULL::timestamp with time zone) < '2019-02-16 14:16:22.646325+00'::timestamp with time zone))
  • Rows Removed by Filter: 667
  • Worker 0: actual time=0.032..6.603 rows=900 loops=1
  • Worker 1: actual time=0.029..0.531 rows=74 loops=1
11. 117.781 122.286 ↓ 1.3 245,940 2

Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_1_187_chunk (cost=3.37..661.33 rows=196,000 width=20) (actual time=0.189..122.286 rows=245,940 loops=2)

  • 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: 50234
  • Worker 1: actual time=0.223..182.872 rows=372995 loops=1
12. 4.505 4.505 ↓ 2.6 500 2

Parallel Seq Scan on _timescaledb_internal.compress_hyper_6_1611_chunk (cost=0.00..661.33 rows=196 width=363) (actual time=0.026..4.505 rows=500 loops=2)

  • Output: compress_hyper_6_1611_chunk._ts_meta_count, compress_hyper_6_1611_chunk."time", compress_hyper_6_1611_chunk.tags_id, compress_hyper_6_1611_chunk.status
  • Filter: ((_timescaledb_internal.segment_meta_get_max(compress_hyper_6_1611_chunk._ts_meta_min_max_1, NULL::timestamp with time zone) >= '2019-02-16 10:16:22.646325+00'::timestamp with time zone) AND (_timescaledb_internal.segment_meta_get_min(compress_hyper_6_1611_chunk._ts_meta_min_max_1, NULL::timestamp with time zone) < '2019-02-16 14:16:22.646325+00'::timestamp with time zone))
  • Rows Removed by Filter: 1001
  • Worker 1: actual time=0.034..6.981 rows=759 loops=1
13. 6.968 6.968 ↑ 1.0 1 3,484

Index Scan using tags_pkey on public.tags t (cost=0.43..2.65 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=3,484)

  • Output: t.id, t.name, t.fleet, t.driver, t.model, t.device_version, t.load_capacity, t.fuel_capacity, t.nominal_fuel_consumption
  • Index Cond: (t.id = _hyper_1_186_chunk.tags_id)
  • Filter: (t.name IS NOT NULL)