explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y6mr

Settings
# exclusive inclusive rows x rows loops node
1. 0.444 23,201.327 ↓ 126.0 126 1

Sort (cost=2,182.56..2,182.56 rows=1 width=182) (actual time=23,201.308..23,201.327 rows=126 loops=1)

  • Sort Key: p.naname, p.vorname, p.man, p.ak, p.pnr, b.ist_tag, b.ist_count
  • Sort Method: quicksort Memory: 56kB
2. 4.637 23,200.883 ↓ 126.0 126 1

Nested Loop Semi Join (cost=35.43..2,182.55 rows=1 width=182) (actual time=21,280.401..23,200.883 rows=126 loops=1)

  • Join Filter: (((zp.man)::text = (ads.man)::text) AND ((zp.ak)::text = (ads.ak)::text) AND ((zp.pnr)::text = (ads.pnr)::text))
3. 8.254 23,137.740 ↓ 8,358.0 8,358 1

Nested Loop (cost=35.14..2,182.00 rows=1 width=153) (actual time=121.796..23,137.740 rows=8,358 loops=1)

4. 7.942 23,095.938 ↓ 8,387.0 8,387 1

Nested Loop Left Join (cost=34.86..2,181.67 rows=1 width=164) (actual time=121.739..23,095.938 rows=8,387 loops=1)

5. 9.348 23,054.508 ↓ 8,372.0 8,372 1

Nested Loop Left Join (cost=34.58..2,181.31 rows=1 width=130) (actual time=121.727..23,054.508 rows=8,372 loops=1)

6. 9.510 20,969.400 ↓ 8,370.0 8,370 1

Nested Loop Left Join (cost=34.15..2,178.94 rows=1 width=118) (actual time=121.160..20,969.400 rows=8,370 loops=1)

7. 6.163 7,802.250 ↓ 8,370.0 8,370 1

Nested Loop Left Join (cost=33.59..2,176.22 rows=1 width=116) (actual time=119.865..7,802.250 rows=8,370 loops=1)

8. 7.229 335.315 ↓ 3,828.0 3,828 1

Nested Loop (cost=33.03..2,173.54 rows=1 width=86) (actual time=117.882..335.315 rows=3,828 loops=1)

  • Join Filter: (((zp.man)::text = (v.man)::text) AND ((zp.ak)::text = (v.ak)::text) AND ((zp.pnr)::text = (v.pnr)::text))
9. 4.985 239.582 ↓ 481.0 3,848 1

Nested Loop (cost=32.61..2,168.97 rows=8 width=69) (actual time=117.790..239.582 rows=3,848 loops=1)

10. 9.367 176.862 ↓ 18.9 3,849 1

Merge Join (cost=32.19..1,957.39 rows=204 width=41) (actual time=117.669..176.862 rows=3,849 loops=1)

  • Merge Cond: (((zp.man)::text = (u.man)::text) AND ((zp.ak)::text = (u.ak)::text) AND ((zp.pnr)::text = (u.pnr)::text))
11. 31.173 31.173 ↑ 1.0 6,357 1

Index Scan using pk_zeitperson on zeitperson zp (cost=0.29..1,069.34 rows=6,449 width=17) (actual time=0.021..31.173 rows=6,357 loops=1)

  • Index Cond: (zeitab <= '2020-05-14'::date)
  • Filter: ((zeitbis >= '2020-05-14'::date) AND ((zeitkzaz)::text = 'A'::text))
  • Rows Removed by Filter: 14282
12. 136.322 136.322 ↓ 1.0 5,911 1

Index Scan using ermusers on users u (cost=0.29..803.83 rows=5,842 width=24) (actual time=0.048..136.322 rows=5,911 loops=1)

  • Index Cond: ((u_ab <= '2020-05-14'::date) AND (u_bis >= '2020-05-14'::date))
  • Filter: ((ug_id)::text = 'ERM'::text)
  • Rows Removed by Filter: 395
13. 57.735 57.735 ↑ 1.0 1 3,849

Index Scan using pk_pgrdat on pgrdat p (cost=0.42..1.03 rows=1 width=28) (actual time=0.014..0.015 rows=1 loops=3,849)

  • Index Cond: (((pnr)::text = (zp.pnr)::text) AND ((man)::text = (zp.man)::text) AND ((ak)::text = (zp.ak)::text) AND (pst_ab <= '2020-05-14'::date))
  • Filter: (pst_bis >= '2020-05-14'::date)
  • Rows Removed by Filter: 2
14. 88.504 88.504 ↑ 1.0 1 3,848

Index Scan using ixr_149 on vertrag v (cost=0.42..0.55 rows=1 width=17) (actual time=0.022..0.023 rows=1 loops=3,848)

  • Index Cond: (((pnr)::text = (p.pnr)::text) AND ((man)::text = (p.man)::text) AND ((ak)::text = (p.ak)::text))
  • Filter: ((ver_ab <= '2020-05-31'::date) AND (ver_bis >= '2020-05-01'::date) AND (verbegin <= '2020-05-14'::date) AND ((verende >= '2020-05-14'::date) OR (verende IS NULL)) AND ((hauptv)::text = '1'::text))
  • Rows Removed by Filter: 4
15. 7,460.772 7,460.772 ↓ 2.0 2 3,828

Index Scan using pk_ist_buchung on ist_buchung b (cost=0.56..2.67 rows=1 width=47) (actual time=1.938..1.949 rows=2 loops=3,828)

  • Index Cond: (((zp.man)::text = (man)::text) AND ((zp.ak)::text = (fnr)::text) AND ((zp.pnr)::text = (pnr)::text) AND (zp.vertnr = vertnr))
  • Filter: ((ist_tag = '2020-05-14'::date) OR (ist_tag = '2020-05-13'::date) OR (ist_tag IS NULL))
  • Rows Removed by Filter: 1188
16. 13,157.640 13,157.640 ↑ 1.0 1 8,370

Index Scan using pk_abr_vortrag on abr_vortrag a (cost=0.56..2.71 rows=1 width=19) (actual time=1.555..1.572 rows=1 loops=8,370)

  • Index Cond: (((zp.man)::text = (man)::text) AND ((zp.ak)::text = (fnr)::text) AND ((zp.pnr)::text = (pnr)::text) AND (abr_tag = '2020-05-14'::date) AND (zp.vertnr = vertnr))
17. 2,075.760 2,075.760 ↓ 0.0 0 8,370

Index Scan using ixr_262 on zeitenkal zk (cost=0.43..2.36 rows=1 width=25) (actual time=0.228..0.248 rows=0 loops=8,370)

  • Index Cond: (((zp.pnr)::text = (pnr)::text) AND ((zp.man)::text = (man)::text) AND ((zp.ak)::text = (ak)::text))
  • Filter: ((zk_vondat <= '2020-05-14'::date) AND ((zk_bisdat >= '2020-05-14'::date) OR (zk_bisdat IS NULL)) AND ((zk_planan)::text = 'I'::text))
  • Rows Removed by Filter: 164
18. 33.488 33.488 ↑ 1.0 1 8,372

Index Scan using ixr_951 on komm_dat k (cost=0.28..0.34 rows=1 width=48) (actual time=0.004..0.004 rows=1 loops=8,372)

  • Index Cond: (((pnr)::text = (u.pnr)::text) AND ((man)::text = (u.man)::text) AND ((ak)::text = (u.ak)::text))
  • Filter: ((km_ab <= '2020-05-14'::date) AND (km_bis >= '2020-05-14'::date) AND (((km_art)::text = 'EMAIA'::text) OR ((km_art)::text = 'TELA'::text)))
  • Rows Removed by Filter: 0
19. 33.548 33.548 ↑ 1.0 1 8,387

Index Scan using pk_orgeinh on orgeinh o (cost=0.28..0.32 rows=1 width=11) (actual time=0.004..0.004 rows=1 loops=8,387)

  • Index Cond: (((oe_orgeinh)::text = (u.orgeinh)::text) AND ((man)::text = (u.orgman)::text) AND ((ak)::text = (u.orgak)::text) AND (oe_ab <= '2020-05-14'::date))
  • Filter: (oe_bis >= '2020-05-14'::date)
  • Rows Removed by Filter: 1
20. 58.506 58.506 ↓ 0.0 0 8,358

Index Only Scan using ermusers on users ads (cost=0.29..0.41 rows=1 width=13) (actual time=0.007..0.007 rows=0 loops=8,358)

  • Index Cond: ((man = (p.man)::text) AND (ak = (p.ak)::text) AND (pnr = (p.pnr)::text) AND (u_ab <= now()) AND (u_bis >= now()))
  • Filter: ((u_id)::text = ANY ('{WEB86041000062,WEB86040000065,WEB86040000203,WEB86040000207,WEB86040000210,WEB86040000265,WEB86040000269,WEB86040000280,WEB86040000385,WEB86040000390,WEB86040000407,WEB86040000412,WEB86040000