explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3NUt

Settings
# exclusive inclusive rows x rows loops node
1. 62.489 11,584.157 ↑ 4,529.2 39 1

GroupAggregate (cost=1,251,583.63..1,259,532.38 rows=176,639 width=265) (actual time=11,533.721..11,584.157 rows=39 loops=1)

  • Group Key: pv.tunnus, pv.tr_numero, pv.nimi, ts.talvisuolaraja
2. 70.821 11,521.668 ↑ 4.4 40,200 1

Sort (cost=1,251,583.63..1,252,025.22 rows=176,639 width=265) (actual time=11,505.449..11,521.668 rows=40,200 loops=1)

  • Sort Key: pv.tunnus, pv.tr_numero, pv.nimi, ts.talvisuolaraja
  • Sort Method: quicksort Memory: 20068kB
3. 46.294 11,450.847 ↑ 4.4 40,200 1

Merge Join (cost=1,233,560.66..1,236,189.14 rows=176,639 width=265) (actual time=11,397.271..11,450.847 rows=40,200 loops=1)

  • Merge Cond: ((pv.tunnus)::text = (rp.pohjavesialue)::text)
4. 108.548 127.821 ↑ 2.2 27,509 1

Sort (cost=9,454.55..9,609.27 rows=61,890 width=227) (actual time=111.094..127.821 rows=27,509 loops=1)

  • Sort Key: pv.tunnus
  • Sort Method: quicksort Memory: 12227kB
5. 19.273 19.273 ↑ 1.9 32,138 1

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

6. 15.172 11,276.732 ↓ 1.1 40,201 1

Materialize (cost=1,224,106.02..1,224,500.41 rows=36,589 width=46) (actual time=11,260.970..11,276.732 rows=40,201 loops=1)

7. 0.376 11,261.560 ↑ 74.8 489 1

Merge Left Join (cost=1,224,106.02..1,224,408.94 rows=36,589 width=46) (actual time=11,260.965..11,261.560 rows=489 loops=1)

  • Merge Cond: ((rp.pohjavesialue)::text = (ts.pohjavesialue)::text)
8. 3.402 11,261.170 ↑ 74.8 489 1

Sort (cost=1,224,055.94..1,224,147.41 rows=36,589 width=14) (actual time=11,260.944..11,261.170 rows=489 loops=1)

  • Sort Key: rp.pohjavesialue
  • Sort Method: quicksort Memory: 472kB
9. 611.370 11,257.768 ↑ 6.4 5,722 1

Hash Join (cost=1,154,939.88..1,221,282.66 rows=36,589 width=14) (actual time=10,157.372..11,257.768 rows=5,722 loops=1)

  • Hash Cond: (rp.toteuma = tot.id)
10. 805.700 805.700 ↓ 1.0 1,507,826 1

Seq Scan on suolatoteuma_reittipiste rp (cost=0.00..60,391.76 rows=1,489,368 width=18) (actual time=0.027..805.700 rows=1,507,826 loops=1)

  • Filter: ((aika >= '2018-12-01 00:00:00'::timestamp without time zone) AND (aika <= '2019-01-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 894758
11. 280.305 9,840.698 ↓ 1.0 491,680 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 21382kB
12. 9,487.128 9,560.393 ↓ 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=108.720..9,560.393 rows=491,680 loops=1)

  • Recheck Cond: (urakka = 270)
  • Heap Blocks: exact=162757
13. 73.265 73.265 ↓ 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=73.265..73.265 rows=585,413 loops=1)

  • Index Cond: (urakka = 270)
14. 0.013 0.014 ↓ 0.0 0 1

Sort (cost=50.08..51.83 rows=700 width=82) (actual time=0.014..0.014 rows=0 loops=1)

  • Sort Key: ts.pohjavesialue
  • Sort Method: quicksort Memory: 25kB
15. 0.001 0.001 ↓ 0.0 0 1

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