explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6ePr

Settings
# exclusive inclusive rows x rows loops node
1. 24,468.024 24,468.024 ↓ 6.0 6 1

Sort (cost=94,719.94..94,719.95 rows=1 width=72) (actual time=24,468.023..24,468.024 rows=6 loops=1)

  • Output: (initcap(replace((data.nombre)::text, 'database.postgres_metrics.karuta_'::text, ''::text))), (((data.fecha_hora)::timestamp(0) without time zone)::text), data.valor
  • Sort Key: (initcap(replace((data.nombre)::text, 'database.postgres_metrics.karuta_'::text, ''::text)))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=17797 read=44168 written=35, temp read=55687 written=82837
2.          

CTE data

3. 3,416.742 20,583.003 ↓ 9,417.8 1,017,120 1

WindowAgg (cost=94,715.33..94,717.49 rows=108 width=182) (actual time=15,366.105..20,583.003 rows=1,017,120 loops=1)

  • Output: ts.id, ts.fecha_hora, ts.valor, ts.data, ts.metrica_id, ts.host_id, m.id, m.nombre, m.metadata, m.host_id, row_number() OVER (?)
  • Buffers: shared hit=17797 read=44168 written=35, temp read=55687 written=55761
4. 11,637.985 17,166.261 ↓ 9,417.8 1,017,120 1

Sort (cost=94,715.33..94,715.60 rows=108 width=174) (actual time=15,366.068..17,166.261 rows=1,017,120 loops=1)

  • Output: ts.id, m.nombre, ts.fecha_hora, ts.valor, ts.data, ts.metrica_id, ts.host_id, m.id, m.metadata, m.host_id
  • Sort Key: m.nombre, ts.id DESC
  • Sort Method: external merge Disk: 208920kB
  • Buffers: shared hit=17797 read=44168 written=35, temp read=55687 written=55761
5. 1,283.183 5,528.276 ↓ 9,417.8 1,017,120 1

Gather (cost=1,005.02..94,711.68 rows=108 width=174) (actual time=1.324..5,528.276 rows=1,017,120 loops=1)

  • Output: ts.id, m.nombre, ts.fecha_hora, ts.valor, ts.data, ts.metrica_id, ts.host_id, m.id, m.metadata, m.host_id
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=17797 read=44168 written=35
6. 578.936 4,245.093 ↓ 7,534.2 339,040 3

Hash Join (cost=5.02..93,700.88 rows=45 width=174) (actual time=0.841..4,245.093 rows=339,040 loops=3)

  • Output: ts.id, m.nombre, ts.fecha_hora, ts.valor, ts.data, ts.metrica_id, ts.host_id, m.id, m.metadata, m.host_id
  • Inner Unique: true
  • Hash Cond: (ts.host_id = h.id)
  • Buffers: shared hit=17797 read=44168 written=35
  • Worker 0: actual time=1.257..4365.666 rows=312220 loops=1
  • Buffers: shared hit=5778 read=13270 written=22
  • Worker 1: actual time=1.054..4377.864 rows=301822 loops=1
  • Buffers: shared hit=5137 read=13265 written=12
7. 1,537.588 3,666.076 ↓ 70.1 631,406 3

Hash Join (cost=3.97..93,675.67 rows=9,013 width=174) (actual time=0.217..3,666.076 rows=631,406 loops=3)

  • Output: ts.id, ts.fecha_hora, ts.valor, ts.data, ts.metrica_id, ts.host_id, m.id, m.nombre, m.metadata, m.host_id
  • Inner Unique: true
  • Hash Cond: (ts.metrica_id = m.id)
  • Buffers: shared hit=17630 read=44168 written=35
  • Worker 0: actual time=0.260..3847.824 rows=581475 loops=1
  • Buffers: shared hit=5695 read=13270 written=22
  • Worker 1: actual time=0.265..3687.941 rows=562281 loops=1
  • Buffers: shared hit=5054 read=13265 written=12
8. 631.489 2,128.335 ↑ 1.3 1,441,818 3

Parallel Append (cost=0.00..88,839.55 rows=1,802,621 width=84) (actual time=0.021..2,128.335 rows=1,441,818 loops=3)

  • Buffers: shared hit=17621 read=44168 written=35
  • Worker 0: actual time=0.025..2197.682 rows=1327235 loops=1
  • Buffers: shared hit=5692 read=13270 written=22
  • Worker 1: actual time=0.022..2192.166 rows=1282379 loops=1
  • Buffers: shared hit=5051 read=13265 written=12
9. 1,496.845 1,496.845 ↑ 1.3 1,441,818 3

Parallel Seq Scan on core.time_series2018_11 ts (cost=0.00..79,812.21 rows=1,802,321 width=84) (actual time=0.018..1,496.845 rows=1,441,818 loops=3)

  • Output: ts.id, ts.fecha_hora, ts.valor, ts.data, ts.metrica_id, ts.host_id
  • Buffers: shared hit=17621 read=44168 written=35
  • Worker 0: actual time=0.023..1573.001 rows=1327235 loops=1
  • Buffers: shared hit=5692 read=13270 written=22
  • Worker 1: actual time=0.020..1550.643 rows=1282379 loops=1
  • Buffers: shared hit=5051 read=13265 written=12
10. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on core.time_series_default ts_1 (cost=0.00..14.24 rows=424 width=84) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: ts_1.id, ts_1.fecha_hora, ts_1.valor, ts_1.data, ts_1.metrica_id, ts_1.host_id
11. 0.041 0.153 ↓ 46.0 46 3

Hash (cost=3.96..3.96 rows=1 width=90) (actual time=0.153..0.153 rows=46 loops=3)

  • Output: m.id, m.nombre, m.metadata, m.host_id
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=9
  • Worker 0: actual time=0.181..0.181 rows=46 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual time=0.182..0.183 rows=46 loops=1
  • Buffers: shared hit=3
12. 0.112 0.112 ↓ 46.0 46 3

Seq Scan on core.metrica m (cost=0.00..3.96 rows=1 width=90) (actual time=0.040..0.112 rows=46 loops=3)

  • Output: m.id, m.nombre, m.metadata, m.host_id
  • Filter: ((m.metadata -> 'tags'::text) ? 'replication'::text)
  • Rows Removed by Filter: 18
  • Buffers: shared hit=9
  • Worker 0: actual time=0.052..0.132 rows=46 loops=1
  • Buffers: shared hit=3
  • Worker 1: actual time=0.051..0.133 rows=46 loops=1
  • Buffers: shared hit=3
13. 0.017 0.081 ↑ 1.0 1 3

Hash (cost=1.04..1.04 rows=1 width=4) (actual time=0.081..0.081 rows=1 loops=3)

  • Output: h.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
  • Worker 0: actual time=0.093..0.093 rows=1 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.107..0.107 rows=1 loops=1
  • Buffers: shared hit=1
14. 0.064 0.064 ↑ 1.0 1 3

Seq Scan on core.host h (cost=0.00..1.04 rows=1 width=4) (actual time=0.061..0.064 rows=1 loops=3)

  • Output: h.id
  • Filter: ((h.nombre)::text = 'espejo_cloud'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=3
  • Worker 0: actual time=0.068..0.072 rows=1 loops=1
  • Buffers: shared hit=1
  • Worker 1: actual time=0.084..0.088 rows=1 loops=1
  • Buffers: shared hit=1