explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qMAl

Settings
# exclusive inclusive rows x rows loops node
1. 0.263 1,377.357 ↓ 126.0 126 1

Sort (cost=2,186.00..2,186.01 rows=1 width=182) (actual time=1,377.340..1,377.357 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.469 1,377.094 ↓ 126.0 126 1

Nested Loop Semi Join (cost=35.43..2,185.99 rows=1 width=182) (actual time=1,120.298..1,377.094 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. 3.969 1,330.830 ↓ 8,359.0 8,359 1

Nested Loop (cost=35.14..2,185.44 rows=1 width=153) (actual time=0.672..1,330.830 rows=8,359 loops=1)

4. 2.184 1,301.697 ↓ 8,388.0 8,388 1

Nested Loop Left Join (cost=34.86..2,185.11 rows=1 width=164) (actual time=0.659..1,301.697 rows=8,388 loops=1)

5. 1.285 1,274.394 ↓ 8,373.0 8,373 1

Nested Loop Left Join (cost=34.58..2,184.76 rows=1 width=130) (actual time=0.653..1,274.394 rows=8,373 loops=1)

6. 5.826 193.250 ↓ 8,371.0 8,371 1

Nested Loop Left Join (cost=34.15..2,182.43 rows=1 width=118) (actual time=0.629..193.250 rows=8,371 loops=1)

7. 3.326 153.940 ↓ 8,371.0 8,371 1

Nested Loop Left Join (cost=33.59..2,179.72 rows=1 width=116) (actual time=0.608..153.940 rows=8,371 loops=1)

8. 4.431 108.506 ↓ 3,828.0 3,828 1

Nested Loop (cost=33.03..2,177.05 rows=1 width=86) (actual time=0.585..108.506 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. 1.561 65.595 ↓ 481.0 3,848 1

Nested Loop (cost=32.61..2,172.47 rows=8 width=69) (actual time=0.562..65.595 rows=3,848 loops=1)

10. 4.927 33.242 ↓ 18.8 3,849 1

Merge Join (cost=32.19..1,960.61 rows=205 width=41) (actual time=0.533..33.242 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. 17.588 17.588 ↑ 1.0 6,357 1

Index Scan using pk_zeitperson on zeitperson zp (cost=0.29..1,072.24 rows=6,490 width=17) (actual time=0.021..17.588 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. 10.727 10.727 ↓ 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.014..10.727 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. 30.792 30.792 ↑ 1.0 1 3,849

Index Scan using pk_pgrdat on pgrdat p (cost=0.42..1.02 rows=1 width=28) (actual time=0.007..0.008 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. 38.480 38.480 ↑ 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.010..0.010 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. 42.108 42.108 ↓ 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=0.009..0.011 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) AND (ist_tag >= '2020-05-13'::date) AND (ist_tag <= '2020-05-14'::date))
16. 33.484 33.484 ↑ 1.0 1 8,371

Index Scan using zie1abrvortrag on abr_vortrag a (cost=0.56..2.70 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=8,371)

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

Index Scan using zie1zeitenkal on zeitenkal zk (cost=0.43..2.32 rows=1 width=26) (actual time=0.129..0.129 rows=0 loops=8,371)

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

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

  • 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. 25.164 25.164 ↑ 1.0 1 8,388

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

  • 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. 41.795 41.795 ↓ 0.0 0 8,359

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

  • 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,WEB86040000198,WEB86040000201,WEB86040000204,WEB86040000208,WEB86040000209,WEB86040000242,WEB86040000253,WEB86040000264,WEB86040000266,WEB86040000267,WEB86040000301,WEB86040000303,WEB86040000305,WEB86040000316,WEB86040000321,WEB86040000334,WEB86040000341,WEB86040000363,WEB86040000374,WEB86040000396,WEB86040000402,WEB86040000404,WEB86041000004,WEB86041000005,WEB86041000008,WEB86041000010,WEB86041000011,WEB86041000013,WEB86041000014,WEB86041000015,WEB86041000016,WEB86041000023,WEB86041000028,WEB86041000031,WEB86041000035,WEB86041000037,WEB86041000038,WEB86041000040,WEB86041000041,WEB86041000042,WEB86041000043,WEB86041000044,WEB86041000045,WEB86041000046,WEB86041000048,WEB86041000049,WEB86041000052,WEB86041000053,WEB86041000054,WEB86041000055,WEB86041000056,WEB86041000057,WEB86041000060,WEB86041000061,WEB86041000024}'::text[]))
  • Rows Removed by Filter: 1
  • Heap Fetches: 8443
Planning time : 18.176 ms
Execution time : 1,377.792 ms