explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eZMC

Settings
# exclusive inclusive rows x rows loops node
1. 0.321 3,904.725 ↑ 97.7 432 1

Sort (cost=360,917.10..361,022.61 rows=42,204 width=76) (actual time=3,904.701..3,904.725 rows=432 loops=1)

  • Sort Key: g.period, g.category_order DESC
  • Sort Method: quicksort Memory: 67kB
  • Buffers: shared hit=1,420,446
  • Functions: 62
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 9.549 ms, Inlining 0.000 ms, Optimization 2.914 ms, Emission 46.282 ms, Total 58.745 ms
2.          

CTE grid

3. 0.115 49.867 ↑ 37.0 432 1

Nested Loop (cost=0.00..211.42 rows=16,000 width=32) (actual time=49.730..49.867 rows=432 loops=1)

  • Buffers: shared hit=1
4. 49.680 49.680 ↑ 41.7 24 1

Function Scan on generate_series period (cost=0.00..10.00 rows=1,000 width=4) (actual time=49.678..49.680 rows=24 loops=1)

5. 0.025 0.072 ↓ 1.1 18 24

Materialize (cost=0.00..1.46 rows=16 width=28) (actual time=0.002..0.003 rows=18 loops=24)

  • Buffers: shared hit=1
6. 0.047 0.047 ↓ 1.1 18 1

Seq Scan on ui_cdc_s5_kategorie_cinnosti (cost=0.00..1.38 rows=16 width=28) (actual time=0.043..0.047 rows=18 loops=1)

  • Filter: ((poradi IS NOT NULL) AND (id <> 1) AND (poradi <= 90))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=1
7.          

CTE zdroj

8. 232.035 3,852.950 ↑ 245.4 430 1

HashAggregate (cost=341,523.70..343,370.11 rows=105,509 width=18) (actual time=3,852.259..3,852.950 rows=430 loops=1)

  • Group Key: date_part('hour'::text, umcz.cas), ui_cdc_s5_vytizeni.id_s5_kategorie_cinnosti
  • Buffers: shared hit=1,420,445
9. 414.909 3,620.915 ↓ 5.7 602,032 1

Nested Loop (cost=1,425.22..340,732.38 rows=105,509 width=14) (actual time=1,592.576..3,620.915 rows=602,032 loops=1)

  • Buffers: shared hit=1,420,445
10. 70.180 1,999.771 ↓ 5.3 241,247 1

Hash Join (cost=1,424.78..261,380.16 rows=45,799 width=16) (actual time=1,592.524..1,999.771 rows=241,247 loops=1)

  • Hash Cond: (m2.org_jednotka_id = oj.klic)
  • Buffers: shared hit=92,353
11. 192.200 1,929.523 ↓ 5.3 241,247 1

Hash Join (cost=1,420.84..260,746.49 rows=45,799 width=20) (actual time=1,592.429..1,929.523 rows=241,247 loops=1)

  • Hash Cond: (umcz.bod_vykonu_kod = m2.kod)
  • Join Filter: ((umcz.cas >= m2.platnost_od) AND (umcz.cas < m2.platnost_do))
  • Rows Removed by Join Filter: 156,439
  • Buffers: shared hit=92,351
12. 1,709.879 1,709.879 ↓ 1.1 241,247 1

Seq Scan on ui_cdc_s5_misto_cas_zdroj umcz (cost=0.00..246,005.98 rows=228,377 width=24) (actual time=1,564.854..1,709.879 rows=241,247 loops=1)

  • Filter: ((cas >= '2019-07-01'::date) AND (cas <= '2019-08-01 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 10,055,609
  • Buffers: shared hit=91,578
13. 16.827 27.444 ↑ 1.3 22,513 1

Hash (cost=1,060.93..1,060.93 rows=28,793 width=20) (actual time=27.444..27.444 rows=22,513 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,400kB
  • Buffers: shared hit=773
14. 10.617 10.617 ↑ 1.3 22,535 1

Seq Scan on ui_cdc_mista_h m2 (cost=0.00..1,060.93 rows=28,793 width=20) (actual time=0.052..10.617 rows=22,535 loops=1)

  • Buffers: shared hit=773
15. 0.017 0.068 ↑ 1.0 86 1

Hash (cost=2.86..2.86 rows=86 width=4) (actual time=0.068..0.068 rows=86 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=2
16. 0.051 0.051 ↑ 1.0 86 1

Seq Scan on ui_cdc_org_jednotky oj (cost=0.00..2.86 rows=86 width=4) (actual time=0.030..0.051 rows=86 loops=1)

  • Buffers: shared hit=2
17. 1,206.235 1,206.235 ↑ 5.5 2 241,247

Index Scan using ui_cdc_s5_vytizeni_id_s5_zdroje_idx on ui_cdc_s5_vytizeni (cost=0.44..1.62 rows=11 width=14) (actual time=0.003..0.005 rows=2 loops=241,247)

  • Index Cond: (id_s5_zdroje = umcz.id_s5_zdroje)
  • Filter: (id_s5_kategorie_cinnosti IS NOT NULL)
  • Buffers: shared hit=1,328,092
18. 0.663 3,904.404 ↑ 97.7 432 1

Merge Left Join (cost=12,350.59..14,093.23 rows=42,204 width=76) (actual time=3,903.690..3,904.404 rows=432 loops=1)

  • Merge Cond: ((g.category = c.category) AND (((g.period)::double precision) = c.datum))
  • Buffers: shared hit=1,420,446
19. 0.371 50.427 ↑ 37.0 432 1

Sort (cost=1,437.26..1,477.26 rows=16,000 width=130) (actual time=50.363..50.427 rows=432 loops=1)

  • Sort Key: g.category, ((g.period)::double precision)
  • Sort Method: quicksort Memory: 66kB
  • Buffers: shared hit=1
20. 50.056 50.056 ↑ 37.0 432 1

CTE Scan on grid g (cost=0.00..320.00 rows=16,000 width=130) (actual time=49.736..50.056 rows=432 loops=1)

  • Buffers: shared hit=1
21. 0.211 3,853.314 ↑ 245.4 430 1

Sort (cost=10,913.33..11,177.10 rows=105,509 width=18) (actual time=3,853.285..3,853.314 rows=430 loops=1)

  • Sort Key: c.category, c.datum
  • Sort Method: quicksort Memory: 58kB
  • Buffers: shared hit=1,420,445
22. 3,853.103 3,853.103 ↑ 245.4 430 1

CTE Scan on zdroj c (cost=0.00..2,110.18 rows=105,509 width=18) (actual time=3,852.267..3,853.103 rows=430 loops=1)

  • Buffers: shared hit=1,420,445JIT: