explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZIss

Settings
# exclusive inclusive rows x rows loops node
1. 490.701 4,407.387 ↑ 4.0 107,047 1

WindowAgg (cost=199,349.66..233,854.78 rows=431,314 width=95) (actual time=3,907.524..4,407.387 rows=107,047 loops=1)

  • Buffers: shared hit=4111 read=18431
2. 110.441 3,916.686 ↑ 4.0 107,047 1

Sort (cost=199,349.66..200,427.94 rows=431,314 width=92) (actual time=3,907.473..3,916.686 rows=107,047 loops=1)

  • Sort Key: dutypart.dienstindex
  • Sort Method: quicksort Memory: 17654kB
  • Buffers: shared hit=4111 read=18431
3. 863.917 3,806.245 ↑ 4.0 107,047 1

Hash Left Join (cost=17,291.22..136,865.66 rows=431,314 width=92) (actual time=610.264..3,806.245 rows=107,047 loops=1)

  • Hash Cond: (dutyreg.registertypindex = drt.idx)
  • Join Filter: (dutyreg.wert >= '0'::numeric)
  • Filter: (((drt.typ = '800'::numeric) AND (dutyreg.wert >= '0'::numeric)) OR (dep.dienstindex IS NULL))
  • Rows Removed by Filter: 1808412
  • Buffers: shared hit=4108 read=18431
4. 1,759.181 2,931.673 ↑ 1.2 1,915,459 1

Hash Right Join (cost=17,289.78..92,546.40 rows=2,256,117 width=97) (actual time=599.564..2,931.673 rows=1,915,459 loops=1)

  • Hash Cond: (dutyreg.dienstindex = duty.idx)
  • Buffers: shared hit=4108 read=18430
5. 573.608 573.608 ↑ 1.0 2,497,051 1

Seq Scan on mdv_dienstregister dutyreg (cost=0.00..43,331.51 rows=2,497,051 width=22) (actual time=0.107..573.608 rows=2,497,051 loops=1)

  • Buffers: shared hit=1830 read=16531
6. 74.638 598.884 ↑ 1.0 107,059 1

Hash (cost=15,951.54..15,951.54 rows=107,059 width=81) (actual time=598.884..598.884 rows=107,059 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 13181kB
  • Buffers: shared hit=2278 read=1899
7. 88.216 524.246 ↑ 1.0 107,059 1

Hash Left Join (cost=7,916.30..15,951.54 rows=107,059 width=81) (actual time=204.571..524.246 rows=107,059 loops=1)

  • Hash Cond: (duty.idx = dutypart.dienstindex)
  • Buffers: shared hit=2278 read=1899
8. 53.021 324.083 ↑ 1.0 107,059 1

Hash Left Join (cost=4,142.39..10,705.63 rows=107,059 width=63) (actual time=92.063..324.083 rows=107,059 loops=1)

  • Hash Cond: (duty.tagesartindex = sdt.idx)
  • Buffers: shared hit=2276 read=793
9. 58.897 270.654 ↑ 1.0 107,059 1

Hash Join (cost=4,120.03..9,333.39 rows=107,059 width=63) (actual time=91.631..270.654 rows=107,059 loops=1)

  • Hash Cond: (dep.dispositionsartindex = dk.idx)
  • Buffers: shared hit=2275 read=784
10. 104.007 211.627 ↑ 1.0 107,059 1

Hash Right Join (cost=4,114.83..7,986.25 rows=107,059 width=58) (actual time=91.484..211.627 rows=107,059 loops=1)

  • Hash Cond: (duty.idx = dep.dienstindex)
  • Buffers: shared hit=2273 read=783
11. 16.699 16.699 ↑ 1.0 118,492 1

Seq Scan on mdv_dienst duty (cost=0.00..2,534.92 rows=118,492 width=17) (actual time=0.020..16.699 rows=118,492 loops=1)

  • Buffers: shared hit=1350
12. 43.092 90.921 ↑ 1.0 107,059 1

Hash (cost=2,776.59..2,776.59 rows=107,059 width=41) (actual time=90.921..90.921 rows=107,059 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 9038kB
  • Buffers: shared hit=923 read=783
13. 47.829 47.829 ↑ 1.0 107,059 1

Seq Scan on mdv_disposition dep (cost=0.00..2,776.59 rows=107,059 width=41) (actual time=0.019..47.829 rows=107,059 loops=1)

  • Buffers: shared hit=923 read=783
14. 0.047 0.130 ↑ 1.0 98 1

Hash (cost=3.98..3.98 rows=98 width=15) (actual time=0.130..0.130 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=2 read=1
15. 0.083 0.083 ↑ 1.0 98 1

Seq Scan on mdv_dispositionsart dk (cost=0.00..3.98 rows=98 width=15) (actual time=0.027..0.083 rows=98 loops=1)

  • Buffers: shared hit=2 read=1
16. 0.137 0.408 ↑ 1.0 549 1

Hash (cost=15.49..15.49 rows=549 width=5) (actual time=0.408..0.408 rows=549 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
  • Buffers: shared hit=1 read=9
17. 0.271 0.271 ↑ 1.0 549 1

Seq Scan on mdv_tagesart sdt (cost=0.00..15.49 rows=549 width=5) (actual time=0.037..0.271 rows=549 loops=1)

  • Buffers: shared hit=1 read=9
18. 49.614 111.947 ↑ 1.0 118,485 1

Hash (cost=2,292.85..2,292.85 rows=118,485 width=18) (actual time=111.947..111.947 rows=118,485 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7154kB
  • Buffers: shared hit=2 read=1106
19. 62.333 62.333 ↑ 1.0 118,485 1

Seq Scan on mdv_dienstteil dutypart (cost=0.00..2,292.85 rows=118,485 width=18) (actual time=0.045..62.333 rows=118,485 loops=1)

  • Buffers: shared hit=2 read=1106
20. 0.006 10.655 ↑ 1.0 1 1

Hash (cost=1.43..1.43 rows=1 width=28) (actual time=10.655..10.655 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
21. 10.649 10.649 ↑ 1.0 1 1

Seq Scan on mdv_dienstregistertyp drt (cost=0.00..1.43 rows=1 width=28) (actual time=10.644..10.649 rows=1 loops=1)

  • Filter: (typ = '800'::numeric)
  • Rows Removed by Filter: 33
  • Buffers: shared read=1