explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qun8

Settings
# exclusive inclusive rows x rows loops node
1. 37.039 2,186.029 ↑ 2,704.1 39 1

GroupAggregate (cost=1,249,200.00..1,253,945.70 rows=105,460 width=265) (actual time=2,126.144..2,186.029 rows=39 loops=1)

  • Group Key: pv.tunnus, pv.tr_numero, pv.nimi, ts.talvisuolaraja
2. 66.458 2,148.990 ↑ 2.5 42,263 1

Sort (cost=1,249,200.00..1,249,463.65 rows=105,460 width=265) (actual time=2,125.790..2,148.990 rows=42,263 loops=1)

  • Sort Key: pv.tunnus, pv.tr_numero, pv.nimi, ts.talvisuolaraja
  • Sort Method: quicksort Memory: 20932kB
3. 21.936 2,082.532 ↑ 2.5 42,263 1

Hash Join (cost=1,182,299.02..1,240,401.29 rows=105,460 width=265) (actual time=1,311.984..2,082.532 rows=42,263 loops=1)

  • Hash Cond: ((rp.pohjavesialue)::text = (pv.tunnus)::text)
4. 3.494 2,014.890 ↑ 4.8 4,565 1

Hash Left Join (cost=1,176,996.50..1,220,254.51 rows=21,845 width=46) (actual time=1,265.718..2,014.890 rows=4,565 loops=1)

  • Hash Cond: ((rp.pohjavesialue)::text = (ts.pohjavesialue)::text)
5. 375.179 2,011.394 ↑ 4.8 4,565 1

Hash Join (cost=1,176,970.75..1,218,214.96 rows=21,845 width=14) (actual time=1,265.702..2,011.394 rows=4,565 loops=1)

  • Hash Cond: (rp.toteuma = tot.id)
6. 372.803 855.032 ↑ 1.0 883,616 1

Bitmap Heap Scan on suolatoteuma_reittipiste rp (cost=22,030.87..59,722.08 rows=889,214 width=18) (actual time=483.832..855.032 rows=883,616 loops=1)

  • Recheck Cond: ((aika >= '2019-01-01 00:00:00'::timestamp without time zone) AND (aika <= '2019-02-01 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=8980
7. 482.229 482.229 ↑ 1.0 883,616 1

Bitmap Index Scan on suolatoteuma_pohjavesialue_aika (cost=0.00..21,808.57 rows=889,214 width=0) (actual time=482.229..482.229 rows=883,616 loops=1)

  • Index Cond: ((aika >= '2019-01-01 00:00:00'::timestamp without time zone) AND (aika <= '2019-02-01 00:00:00'::timestamp without time zone))
8. 256.922 781.183 ↓ 1.0 491,680 1

Hash (cost=1,148,908.13..1,148,908.13 rows=482,540 width=4) (actual time=781.183..781.183 rows=491,680 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 21382kB
9. 441.983 524.261 ↓ 1.0 491,680 1

Bitmap Heap Scan on toteuma tot (cost=16,652.25..1,148,908.13 rows=482,540 width=4) (actual time=122.433..524.261 rows=491,680 loops=1)

  • Recheck Cond: (urakka = 270)
  • Heap Blocks: exact=162757
10. 82.278 82.278 ↓ 1.2 585,413 1

Bitmap Index Scan on toteuma_urakka_idx (cost=0.00..16,531.61 rows=482,540 width=0) (actual time=82.278..82.278 rows=585,413 loops=1)

  • Index Cond: (urakka = 270)
11. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=17.00..17.00 rows=700 width=82) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
12. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on pohjavesialue_talvisuola ts (cost=0.00..17.00 rows=700 width=82) (actual time=0.002..0.002 rows=0 loops=1)

13. 23.481 45.706 ↑ 1.9 32,138 1

Hash (cost=4,528.90..4,528.90 rows=61,890 width=227) (actual time=45.706..45.706 rows=32,138 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 8686kB
14. 22.225 22.225 ↑ 1.9 32,138 1

Seq Scan on pohjavesialue pv (cost=0.00..4,528.90 rows=61,890 width=227) (actual time=0.005..22.225 rows=32,138 loops=1)