explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TuFo

Settings
# exclusive inclusive rows x rows loops node
1. 24.301 9,791.513 ↓ 160.5 13,323 1

Sort (cost=103,042.85..103,043.06 rows=83 width=216) (actual time=9,790.296..9,791.513 rows=13,323 loops=1)

  • Sort Key: tbw.fallid, tbw.startdate, tbw.missingdate
  • Sort Method: quicksort Memory: 3,902kB
  • Functions: 387
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 39.137 ms, Inlining 0.000 ms, Optimization 12.335 ms, Emission 211.855 ms, Total 263.327 ms
2.          

CTE bewegdata

3. 44.318 9,436.040 ↓ 4.0 70,579 1

Subquery Scan on abc (cost=8,634.69..100,098.65 rows=17,784 width=39) (actual time=258.103..9,436.040 rows=70,579 loops=1)

4. 804.207 9,391.722 ↓ 4.0 70,579 1

Nested Loop (cost=8,634.69..99,831.89 rows=17,784 width=79) (actual time=258.099..9,391.722 rows=70,579 loops=1)

  • Join Filter: ((((rownum - 1)))::numeric <= (SubPlan 1))
  • Rows Removed by Join Filter: 141,997
5. 48.740 7,882.083 ↑ 3.9 364 1

Result (cost=3,848.91..34,533.22 rows=1,404 width=8) (actual time=242.663..7,882.083 rows=364 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
6. 0.631 7,833.343 ↓ 1.9 2,725 1

Append (cost=3,848.91..34,515.67 rows=1,404 width=0) (actual time=242.595..7,833.343 rows=2,725 loops=1)

7. 555.085 915.833 ↓ 3.5 364 1

Subquery Scan on *SELECT* 1 (cost=3,848.91..5,790.13 rows=105 width=0) (actual time=242.594..915.833 rows=364 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
  • Rows Removed by Filter: 32,422
8. 19.288 360.748 ↓ 104.4 32,786 1

Nested Loop (cost=3,848.91..5,703.78 rows=314 width=192) (actual time=240.825..360.748 rows=32,786 loops=1)

9. 7.043 275.888 ↓ 59.2 32,786 1

Hash Join (cost=3,848.61..5,367.67 rows=554 width=4) (actual time=240.805..275.888 rows=32,786 loops=1)

  • Hash Cond: (c.relnamespace = n.oid)
10. 22.184 49.519 ↓ 19.8 32,920 1

Hash Join (cost=3,783.43..5,298.11 rows=1,665 width=8) (actual time=21.407..49.519 rows=32,920 loops=1)

  • Hash Cond: (x.indrelid = c.oid)
11. 5.974 5.974 ↓ 1.0 41,332 1

Seq Scan on pg_index x (cost=0.00..1,406.20 rows=41,320 width=8) (actual time=0.007..5.974 rows=41,332 loops=1)

12. 1.037 21.361 ↓ 3.0 8,757 1

Hash (cost=3,746.58..3,746.58 rows=2,948 width=8) (actual time=21.361..21.361 rows=8,757 loops=1)

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 471kB
13. 20.324 20.324 ↓ 3.0 8,757 1

Seq Scan on pg_class c (cost=0.00..3,746.58 rows=2,948 width=8) (actual time=0.025..20.324 rows=8,757 loops=1)

  • Filter: ((relkind = ANY ('{r,p}'::char[])) AND has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text))
  • Rows Removed by Filter: 64,439
14. 0.014 219.326 ↑ 25.8 21 1

Hash (cost=58.42..58.42 rows=541 width=4) (actual time=219.326..219.326 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 219.312 219.312 ↑ 25.8 21 1

Seq Scan on pg_namespace n (cost=0.00..58.42 rows=541 width=4) (actual time=215.717..219.312 rows=21 loops=1)

  • Filter: (has_schema_privilege(oid, 'USAGE'::text) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
  • Rows Removed by Filter: 1,605
16. 65.572 65.572 ↑ 1.0 1 32,786

Index Scan using pg_class_oid_index on pg_class i (cost=0.29..0.61 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=32,786)

  • Index Cond: (oid = x.indexrelid)
  • Filter: (relkind = ANY ('{i,I}'::char[]))
17. 322.458 6,035.571 ↑ 1.6 364 1

Subquery Scan on all_sequences (cost=65.19..7,649.86 rows=599 width=0) (actual time=2.024..6,035.571 rows=364 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
  • Rows Removed by Filter: 9,522
18. 5,688.575 5,713.113 ↓ 5.5 9,886 1

Hash Join (cost=65.19..7,155.41 rows=1,798 width=320) (actual time=1.945..5,713.113 rows=9,886 loops=1)

  • Hash Cond: (c_1.relnamespace = n_1.oid)
19. 23.971 23.971 ↓ 1.8 9,886 1

Seq Scan on pg_class c_1 (cost=0.00..3,929.51 rows=5,403 width=8) (actual time=0.029..23.971 rows=9,886 loops=1)

  • Filter: ((relkind = 'S'::char) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, UPDATE'::text)))
  • Rows Removed by Filter: 63,310
20. 0.007 0.567 ↑ 25.8 21 1

Hash (cost=58.42..58.42 rows=541 width=4) (actual time=0.567..0.567 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.560 0.560 ↑ 25.8 21 1

Seq Scan on pg_namespace n_1 (cost=0.00..58.42 rows=541 width=4) (actual time=0.030..0.560 rows=21 loops=1)

  • Filter: (has_schema_privilege(oid, 'USAGE'::text) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
  • Rows Removed by Filter: 1,605
22. 136.240 165.429 ↓ 1.1 364 1

Subquery Scan on *SELECT* 3 (cost=65.19..4,089.30 rows=327 width=0) (actual time=0.729..165.429 rows=364 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
  • Rows Removed by Filter: 8,319
23. 2.834 29.189 ↓ 8.9 8,683 1

Hash Join (cost=65.19..3,819.53 rows=981 width=184) (actual time=0.679..29.189 rows=8,683 loops=1)

  • Hash Cond: (c_2.relnamespace = n_2.oid)
24. 25.739 25.739 ↓ 3.0 8,757 1

Seq Scan on pg_class c_2 (cost=0.00..3,746.58 rows=2,948 width=8) (actual time=0.048..25.739 rows=8,757 loops=1)

  • Filter: ((NOT relispartition) AND (relkind = ANY ('{r,p}'::char[])) AND has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text))
  • Rows Removed by Filter: 64,439
25. 0.013 0.616 ↑ 25.8 21 1

Hash (cost=58.42..58.42 rows=541 width=4) (actual time=0.616..0.616 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.603 0.603 ↑ 25.8 21 1

Seq Scan on pg_namespace n_2 (cost=0.00..58.42 rows=541 width=4) (actual time=0.022..0.603 rows=21 loops=1)

  • Filter: (has_schema_privilege(oid, 'USAGE'::text) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
  • Rows Removed by Filter: 1,605
27. 0.062 0.132 ↑ 1.0 1 1

Subquery Scan on *SELECT* 4 (cost=0.28..9.60 rows=1 width=0) (actual time=0.131..0.132 rows=1 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
28. 0.031 0.070 ↑ 1.0 1 1

Nested Loop (cost=0.28..9.32 rows=1 width=192) (actual time=0.069..0.070 rows=1 loops=1)

29. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on pg_synonym sy (cost=0.00..1.01 rows=1 width=25) (actual time=0.024..0.025 rows=1 loops=1)

30. 0.014 0.014 ↑ 1.0 1 1

Index Scan using pg_namespace_oid_index on pg_namespace pnname (cost=0.28..8.30 rows=1 width=68) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (oid = sy.synnamespace)
  • Filter: ((nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])) AND ((nspname = 'public'::name) OR pg_has_role(sy.synowner, 'USAGE'::text)))
31. 75.075 311.471 ↓ 2.1 364 1

Subquery Scan on *SELECT* 5 (cost=65.19..3,960.86 rows=176 width=0) (actual time=0.738..311.471 rows=364 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
  • Rows Removed by Filter: 4,162
32. 1.808 236.396 ↓ 8.6 4,526 1

Hash Join (cost=65.19..3,815.93 rows=527 width=192) (actual time=0.679..236.396 rows=4,526 loops=1)

  • Hash Cond: (c_3.relnamespace = n_3.oid)
33. 233.976 233.976 ↓ 2.9 4,644 1

Seq Scan on pg_class c_3 (cost=0.00..3,746.58 rows=1,583 width=4) (actual time=0.050..233.976 rows=4,644 loops=1)

  • Filter: (has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) AND (relkind = 'v'::char))
  • Rows Removed by Filter: 68,552
34. 0.008 0.612 ↑ 25.8 21 1

Hash (cost=58.42..58.42 rows=541 width=4) (actual time=0.612..0.612 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.604 0.604 ↑ 25.8 21 1

Seq Scan on pg_namespace n_3 (cost=0.00..58.42 rows=541 width=4) (actual time=0.017..0.604 rows=21 loops=1)

  • Filter: (has_schema_privilege(oid, 'USAGE'::text) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
  • Rows Removed by Filter: 1,605
36. 12.640 43.608 ↓ 11.0 364 1

Subquery Scan on *SELECT* 6 (cost=69.03..1,189.25 rows=33 width=0) (actual time=7.506..43.608 rows=364 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
  • Rows Removed by Filter: 242
37. 0.191 30.968 ↓ 6.2 606 1

Nested Loop (cost=69.03..1,162.30 rows=98 width=192) (actual time=7.408..30.968 rows=606 loops=1)

38. 0.511 29.565 ↓ 6.2 606 1

Nested Loop (cost=68.74..620.34 rows=98 width=4) (actual time=7.392..29.565 rows=606 loops=1)

  • Join Filter: (pr.prolang = ln.oid)
  • Rows Removed by Join Filter: 1,818
39. 0.492 29.054 ↓ 6.2 606 1

Hash Join (cost=68.74..612.93 rows=98 width=8) (actual time=7.348..29.054 rows=606 loops=1)

  • Hash Cond: (pr.pronamespace = ns.oid)
40. 27.805 27.805 ↓ 3.0 954 1

Seq Scan on pg_proc pr (cost=0.00..543.36 rows=318 width=12) (actual time=6.570..27.805 rows=954 loops=1)

  • Filter: (has_function_privilege(oid, 'EXECUTE'::text) AND (protype = '1'::char))
  • Rows Removed by Filter: 4,670
41. 0.007 0.757 ↑ 100.0 5 1

Hash (cost=62.49..62.49 rows=500 width=4) (actual time=0.757..0.757 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
42. 0.750 0.750 ↑ 100.0 5 1

Seq Scan on pg_namespace ns (cost=0.00..62.49 rows=500 width=4) (actual time=0.034..0.750 rows=5 loops=1)

  • Filter: (has_schema_privilege(oid, 'USAGE'::text) AND (nspparent = '0'::oid) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
  • Rows Removed by Filter: 1,621
43. 0.000 0.000 ↑ 1.2 4 606

Materialize (cost=0.00..1.07 rows=5 width=4) (actual time=0.000..0.000 rows=4 loops=606)

44. 0.028 0.028 ↑ 1.2 4 1

Seq Scan on pg_language ln (cost=0.00..1.05 rows=5 width=4) (actual time=0.027..0.028 rows=4 loops=1)

45. 1.212 1.212 ↑ 1.0 1 606

Index Scan using pg_type_oid_index on pg_type tp_2 (cost=0.29..5.53 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=606)

  • Index Cond: (oid = pr.prorettype)
  • Filter: (typname <> 'trigger'::name)
46. 6.388 13.328 ↓ 2.8 364 1

Subquery Scan on *SELECT* 7 (cost=70.14..2,076.93 rows=129 width=0) (actual time=2.686..13.328 rows=364 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
  • Rows Removed by Filter: 4
47. 0.162 6.940 ↑ 1.0 368 1

Nested Loop (cost=70.14..1,970.78 rows=386 width=192) (actual time=2.626..6.940 rows=368 loops=1)

48. 0.110 6.038 ↑ 1.0 370 1

Hash Join (cost=69.85..618.39 rows=386 width=4) (actual time=2.611..6.038 rows=370 loops=1)

  • Hash Cond: (pr_1.prolang = ln_1.oid)
49. 0.433 5.899 ↑ 1.0 370 1

Hash Join (cost=68.74..615.40 rows=386 width=8) (actual time=2.571..5.899 rows=370 loops=1)

  • Hash Cond: (pr_1.pronamespace = ns_1.oid)
50. 4.741 4.741 ↓ 3.0 3,721 1

Seq Scan on pg_proc pr_1 (cost=0.00..543.36 rows=1,255 width=12) (actual time=0.008..4.741 rows=3,721 loops=1)

  • Filter: (has_function_privilege(oid, 'EXECUTE'::text) AND (protype = '0'::char))
  • Rows Removed by Filter: 1,903
51. 0.004 0.725 ↑ 100.0 5 1

Hash (cost=62.49..62.49 rows=500 width=4) (actual time=0.725..0.725 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 0.721 0.721 ↑ 100.0 5 1

Seq Scan on pg_namespace ns_1 (cost=0.00..62.49 rows=500 width=4) (actual time=0.018..0.721 rows=5 loops=1)

  • Filter: (has_schema_privilege(oid, 'USAGE'::text) AND (nspparent = '0'::oid) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
  • Rows Removed by Filter: 1,621
53. 0.006 0.029 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=4) (actual time=0.029..0.029 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
54. 0.023 0.023 ↑ 1.0 5 1

Seq Scan on pg_language ln_1 (cost=0.00..1.05 rows=5 width=4) (actual time=0.022..0.023 rows=5 loops=1)

55. 0.740 0.740 ↑ 1.0 1 370

Index Scan using pg_type_oid_index on pg_type tp_3 (cost=0.29..3.50 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=370)

  • Index Cond: (oid = pr_1.prorettype)
  • Filter: (typname <> 'trigger'::name)
  • Rows Removed by Filter: 0
56. 1.518 1.956 ↓ 19.6 98 1

Subquery Scan on *SELECT* 8 (cost=62.02..68.24 rows=5 width=0) (actual time=0.405..1.956 rows=98 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
57. 0.032 0.438 ↓ 7.0 98 1

Merge Join (cost=62.02..64.39 rows=14 width=192) (actual time=0.369..0.438 rows=98 loops=1)

  • Merge Cond: (ns_2.oid = pg_namespace_2.nspparent)
58. 0.067 0.067 ↑ 90.2 6 1

Index Scan using pg_namespace_oid_index on pg_namespace ns_2 (cost=0.28..107.83 rows=541 width=4) (actual time=0.033..0.067 rows=6 loops=1)

  • Filter: (has_schema_privilege(oid, 'USAGE'::text) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
  • Rows Removed by Filter: 35
59. 0.027 0.339 ↓ 2.4 98 1

Sort (cost=61.55..61.66 rows=41 width=4) (actual time=0.328..0.339 rows=98 loops=1)

  • Sort Key: pg_namespace_2.nspparent
  • Sort Method: quicksort Memory: 29kB
60. 0.312 0.312 ↓ 2.4 98 1

Seq Scan on pg_namespace pg_namespace_2 (cost=0.00..60.45 rows=41 width=4) (actual time=0.014..0.312 rows=98 loops=1)

  • Filter: ((NOT nspcompoundtrigger) AND (nspparent <> '0'::oid) AND has_package_privilege(oid, 'EXECUTE'::text) AND (nspobjecttype = '0'::oid))
  • Rows Removed by Filter: 1,528
61. 0.614 17.394 ↓ 7.8 31 1

Subquery Scan on *SELECT* 9 (cost=66.05..71.98 rows=4 width=0) (actual time=16.830..17.394 rows=31 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
62. 0.034 16.780 ↓ 2.4 31 1

Merge Join (cost=66.05..68.41 rows=13 width=192) (actual time=16.721..16.780 rows=31 loops=1)

  • Merge Cond: (ns_3.oid = pg_namespace_3.nspparent)
63. 0.077 0.077 ↑ 90.2 6 1

Index Scan using pg_namespace_oid_index on pg_namespace ns_3 (cost=0.28..107.83 rows=541 width=4) (actual time=0.034..0.077 rows=6 loops=1)

  • Filter: (has_schema_privilege(oid, 'USAGE'::text) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
  • Rows Removed by Filter: 35
64. 0.028 16.669 ↑ 1.3 31 1

Sort (cost=65.58..65.68 rows=40 width=4) (actual time=16.664..16.669 rows=31 loops=1)

  • Sort Key: pg_namespace_3.nspparent
  • Sort Method: quicksort Memory: 26kB
65. 16.641 16.641 ↑ 1.3 31 1

Seq Scan on pg_namespace pg_namespace_3 (cost=0.00..64.52 rows=40 width=4) (actual time=0.317..16.641 rows=31 loops=1)

  • Filter: ((NOT nspcompoundtrigger) AND (nspparent <> '0'::oid) AND (edb_get_packagebodydef(oid, false) IS NOT NULL) AND has_package_privilege(oid, 'EXECUTE'::text) AND (nspobjecttype = '0'::oid))
  • Rows Removed by Filter: 1,595
66. 16.835 106.316 ↓ 15.8 364 1

Subquery Scan on *SELECT* 10 (cost=3,762.82..3,944.97 rows=23 width=0) (actual time=88.458..106.316 rows=364 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
  • Rows Removed by Filter: 544
67. 0.547 89.481 ↓ 13.4 908 1

Hash Join (cost=3,762.82..3,926.27 rows=68 width=192) (actual time=88.344..89.481 rows=908 loops=1)

  • Hash Cond: (t.tgrelid = c_4.oid)
68. 0.673 0.673 ↓ 1.0 922 1

Index Scan using pg_trigger_tgconstraint_index on pg_trigger t (cost=0.29..159.61 rows=921 width=4) (actual time=0.042..0.673 rows=922 loops=1)

  • Index Cond: (tgconstraint = '0'::oid)
  • Filter: (((tgtype)::integer & 60) <> 0)
69. 8.139 88.261 ↓ 10.3 55,886 1

Hash (cost=3,694.84..3,694.84 rows=5,415 width=4) (actual time=88.261..88.261 rows=55,886 loops=1)

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,477kB
70. 11.970 80.122 ↓ 10.3 55,886 1

Hash Join (cost=67.00..3,694.84 rows=5,415 width=4) (actual time=0.588..80.122 rows=55,886 loops=1)

  • Hash Cond: (c_4.relnamespace = n_4.oid)
71. 67.592 67.592 ↓ 3.0 72,617 1

Seq Scan on pg_class c_4 (cost=0.00..3,563.65 rows=24,391 width=8) (actual time=0.013..67.592 rows=72,617 loops=1)

  • Filter: has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
  • Rows Removed by Filter: 579
72. 0.008 0.560 ↑ 17.2 21 1

Hash (cost=62.49..62.49 rows=361 width=4) (actual time=0.560..0.560 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
73. 0.552 0.552 ↑ 17.2 21 1

Seq Scan on pg_namespace n_4 (cost=0.00..62.49 rows=361 width=4) (actual time=0.019..0.552 rows=21 loops=1)

  • Filter: ((NOT pg_is_other_temp_schema(oid)) AND has_schema_privilege(oid, 'USAGE'::text) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
  • Rows Removed by Filter: 1,605
74. 1.027 217.025 ↓ 45.0 45 1

Subquery Scan on *SELECT* 11 (cost=122.85..5,163.38 rows=1 width=0) (actual time=6.202..217.025 rows=45 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
75. 18.948 215.998 ↓ 15.0 45 1

Nested Loop Left Join (cost=122.85..5,162.56 rows=3 width=192) (actual time=6.108..215.998 rows=45 loops=1)

  • Filter: ((c_5.relkind IS NULL) OR (c_5.relkind = 'c'::char))
  • Rows Removed by Filter: 23,200
76. 6.832 173.805 ↓ 11.7 23,245 1

Hash Join (cost=122.56..2,501.75 rows=1,993 width=4) (actual time=4.983..173.805 rows=23,245 loops=1)

  • Hash Cond: (t_1.typnamespace = n_5.oid)
77. 162.098 162.098 ↓ 2.7 32,070 1

Seq Scan on pg_type t_1 (cost=0.00..2,347.60 rows=12,005 width=8) (actual time=0.053..162.098 rows=32,070 loops=1)

  • Filter: (has_type_privilege(oid, 'USAGE'::text) AND ((typelem = '0'::oid) OR (typlen <> '-1'::integer) OR (typarray <> '0'::oid)))
  • Rows Removed by Filter: 13,575
78. 0.008 4.875 ↑ 15.0 18 1

Hash (cost=119.18..119.18 rows=270 width=4) (actual time=4.875..4.875 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
79. 1.319 4.867 ↑ 15.0 18 1

Seq Scan on pg_namespace n_5 (cost=56.70..119.18 rows=270 width=4) (actual time=4.249..4.867 rows=18 loops=1)

  • Filter: ((NOT (hashed SubPlan 2)) AND has_schema_privilege(oid, 'USAGE'::text) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
  • Rows Removed by Filter: 1,608
80.          

SubPlan (for Seq Scan)

81. 3.548 3.548 ↑ 1.2 106 1

Seq Scan on pg_namespace (cost=0.00..56.39 rows=122 width=64) (actual time=3.322..3.548 rows=106 loops=1)

  • Filter: ((NOT nspcompoundtrigger) AND (nspparent <> '0'::oid) AND (nspobjecttype = '0'::oid))
  • Rows Removed by Filter: 1,520
82. 23.245 23.245 ↑ 1.0 1 23,245

Index Scan using pg_class_oid_index on pg_class c_5 (cost=0.29..1.32 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=23,245)

  • Index Cond: (oid = t_1.typrelid)
83. 0.103 4.649 ↓ 2.0 2 1

Subquery Scan on *SELECT* 12 (cost=57.56..494.14 rows=1 width=0) (actual time=4.381..4.649 rows=2 loops=1)

  • Filter: ((rownum)::numeric < GREATEST('365'::numeric, (fgetdatedifference(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) + '1'::numeric)))
84. 0.010 4.546 ↓ 2.0 2 1

Nested Loop (cost=57.56..493.87 rows=1 width=192) (actual time=4.304..4.546 rows=2 loops=1)

85. 0.014 0.302 ↓ 2.0 2 1

Nested Loop Left Join (cost=0.58..436.86 rows=1 width=4) (actual time=0.074..0.302 rows=2 loops=1)

  • Filter: ((c_6.relkind IS NULL) OR (c_6.relkind = 'c'::char))
86. 0.029 0.278 ↑ 6.5 2 1

Nested Loop (cost=0.29..419.50 rows=13 width=8) (actual time=0.060..0.278 rows=2 loops=1)

87. 0.201 0.201 ↑ 1.0 48 1

Seq Scan on pg_namespace pg_namespace_4 (cost=0.00..48.26 rows=48 width=4) (actual time=0.031..0.201 rows=48 loops=1)

  • Filter: (nspbodysrc IS NOT NULL)
  • Rows Removed by Filter: 1,578
88. 0.048 0.048 ↓ 0.0 0 48

Index Scan using pg_type_oid_index on pg_type t_2 (cost=0.29..7.73 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=48)

  • Index Cond: (oid = pg_namespace_4.nspobjecttype)
  • Filter: (has_type_privilege(oid, 'USAGE'::text) AND ((typelem = '0'::oid) OR (typlen <> '-1'::integer) OR (typarray <> '0'::oid)))
89. 0.010 0.010 ↑ 1.0 1 2

Index Scan using pg_class_oid_index on pg_class c_6 (cost=0.29..1.32 rows=1 width=5) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (oid = t_2.typrelid)
90. 0.688 4.234 ↑ 1.0 1 2

Index Scan using pg_namespace_oid_index on pg_namespace n_6 (cost=56.97..57.01 rows=1 width=4) (actual time=2.117..2.117 rows=1 loops=2)

  • Index Cond: (oid = t_2.typnamespace)
  • Filter: ((NOT (hashed SubPlan 3)) AND has_schema_privilege(oid, 'USAGE'::text) AND (nspname <> ALL ('{pg_catalog,information_schema,pg_toast}'::name[])))
91.          

SubPlan (for Index Scan)

92. 3.546 3.546 ↑ 1.2 106 1

Seq Scan on pg_namespace pg_namespace_1 (cost=0.00..56.39 rows=122 width=64) (actual time=3.338..3.546 rows=106 loops=1)

  • Filter: ((NOT nspcompoundtrigger) AND (nspparent <> '0'::oid) AND (nspobjecttype = '0'::oid))
  • Rows Removed by Filter: 1,520
93. 22.650 67.704 ↓ 15.4 584 364

Materialize (cost=4,785.78..4,819.13 rows=38 width=103) (actual time=0.042..0.186 rows=584 loops=364)

94. 29.921 45.054 ↓ 15.4 584 1

Result (cost=4,785.78..4,818.56 rows=38 width=103) (actual time=15.323..45.054 rows=584 loops=1)

95. 0.383 15.133 ↓ 15.4 584 1

Sort (cost=4,785.78..4,785.88 rows=38 width=95) (actual time=15.034..15.133 rows=584 loops=1)

  • Sort Key: tb.fallid, tb.fachlicheoe, tb.pflegerischeoe
  • Sort Method: quicksort Memory: 107kB
96. 1.200 14.750 ↓ 15.4 584 1

Nested Loop (cost=107.04..4,784.78 rows=38 width=95) (actual time=5.618..14.750 rows=584 loops=1)

97. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on katpprparameter (cost=0.00..1.09 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)

  • Filter: ((einrichtung)::text = 'Ein1'::text)
  • Rows Removed by Filter: 6
98. 0.161 13.538 ↓ 15.4 584 1

Hash Join (cost=107.04..4,782.84 rows=38 width=47) (actual time=5.544..13.538 rows=584 loops=1)

  • Hash Cond: ((tb.pflegerischeoe)::text = (katoe.oe)::text)
99. 8.682 13.139 ↓ 12.6 592 1

Bitmap Heap Scan on tblbewegung tb (cost=85.04..4,760.71 rows=47 width=47) (actual time=5.293..13.139 rows=592 loops=1)

  • Recheck Cond: (((bewegungsdatum >= add_months(to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), '-6'::numeric)) AND (bewegungsdatum <= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying))) OR ((bewegungsdatum >= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (bewegungsdatum <= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying))) OR ((bewegungsdatum >= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (bewegungsdatum <= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying))) OR ((bewegungsdatum >= add_months(to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), '-6'::numeric)) AND (bewegungsdatum <= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying))))
  • Filter: ((stornierer IS NULL) AND ((bewegungstyp)::text = ANY ('{Aufnahme,Verlegung}'::text[])) AND ((status IS NULL) OR ((status)::text <> 'P'::text)) AND (NOT (hashed SubPlan 4)) AND ((einrichtung)::text = 'Ein1'::text) AND (((bewegungsdatum >= add_months(to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), '-6'::numeric)) AND (bewegungsdatum <= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (((endedatum >= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (endedatum <= add_months(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), '6'::numeric))) OR (endedatum = to_date('31.12.9990'::character varying, 'DD.MM.YYYY'::character varying)))) OR ((bewegungsdatum >= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (bewegungsdatum <= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (((endedatum >= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (endedatum <= add_months(to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), '6'::numeric))) OR (endedatum = to_date('31.12.9990'::character varying, 'DD.MM.YYYY'::character varying)))) OR ((bewegungsdatum >= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (bewegungsdatum <= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (((endedatum >= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (endedatum <= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying))) OR (endedatum = to_date('31.12.9990'::character varying, 'DD.MM.YYYY'::character varying)))) OR ((bewegungsdatum >= add_months(to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), '-6'::numeric)) AND (bewegungsdatum <= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (((endedatum >= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (endedatum <= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying))) OR (endedatum = to_date('31.12.9990'::character varying, 'DD.MM.YYYY'::character varying))))))
  • Rows Removed by Filter: 4,807
  • Heap Blocks: exact=413
100. 0.002 0.647 ↓ 0.0 0 1

BitmapOr (cost=50.12..50.12 rows=1,636 width=0) (actual time=0.647..0.647 rows=0 loops=1)

101. 0.403 0.403 ↓ 7.3 5,180 1

Bitmap Index Scan on xie2tblbewegung (cost=0.00..19.54 rows=711 width=0) (actual time=0.403..0.403 rows=5,180 loops=1)

  • Index Cond: ((bewegungsdatum >= add_months(to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), '-6'::numeric)) AND (bewegungsdatum <= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)))
102. 0.029 0.029 ↓ 2.3 251 1

Bitmap Index Scan on xie2tblbewegung (cost=0.00..5.50 rows=107 width=0) (actual time=0.029..0.029 rows=251 loops=1)

  • Index Cond: ((bewegungsdatum >= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (bewegungsdatum <= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)))
103. 0.014 0.014 ↓ 2.3 251 1

Bitmap Index Scan on xie2tblbewegung (cost=0.00..5.50 rows=107 width=0) (actual time=0.014..0.014 rows=251 loops=1)

  • Index Cond: ((bewegungsdatum >= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (bewegungsdatum <= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)))
104. 0.199 0.199 ↓ 7.3 5,180 1

Bitmap Index Scan on xie2tblbewegung (cost=0.00..19.54 rows=711 width=0) (actual time=0.199..0.199 rows=5,180 loops=1)

  • Index Cond: ((bewegungsdatum >= add_months(to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying), '-6'::numeric)) AND (bewegungsdatum <= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)))
105.          

SubPlan (for Bitmap Heap Scan)

106. 3.810 3.810 ↑ 1.0 13 1

Seq Scan on katbewegungsart (cost=0.00..34.89 rows=13 width=8) (actual time=3.532..3.810 rows=13 loops=1)

  • Filter: ((hostcode)::text = ANY ('{B,MB}'::text[]))
  • Rows Removed by Filter: 1,098
107. 0.055 0.238 ↑ 1.0 391 1

Hash (cost=17.02..17.02 rows=399 width=7) (actual time=0.237..0.238 rows=391 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
108. 0.183 0.183 ↑ 1.0 391 1

Seq Scan on katoe (cost=0.00..17.02 rows=399 width=7) (actual time=0.018..0.183 rows=391 loops=1)

  • Filter: ((nvl(oenichtppr, 'N'::character varying))::text <> 'J'::text)
  • Rows Removed by Filter: 10
109.          

SubPlan (for Nested Loop)

110. 637.728 637.728 ↑ 1.0 1 212,576

Seq Scan on dual (cost=0.00..1.03 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=212,576)

111. 1.402 9,767.212 ↓ 160.5 13,323 1

Append (cost=1.27..2,941.56 rows=83 width=216) (actual time=291.543..9,767.212 rows=13,323 loops=1)

112. 21.978 9,691.043 ↓ 161.8 13,265 1

Nested Loop (cost=1.27..1,660.99 rows=82 width=216) (actual time=291.542..9,691.043 rows=13,265 loops=1)

113. 5.251 9,629.270 ↓ 161.8 13,265 1

Nested Loop (cost=0.85..1,594.39 rows=82 width=168) (actual time=291.503..9,629.270 rows=13,265 loops=1)

114. 24.825 9,584.224 ↓ 157.9 13,265 1

Nested Loop Anti Join (cost=0.43..900.53 rows=84 width=138) (actual time=291.461..9,584.224 rows=13,265 loops=1)

115. 9,532.751 9,532.751 ↓ 149.7 13,324 1

CTE Scan on bewegdata tbw (cost=0.00..533.52 rows=89 width=138) (actual time=258.690..9,532.751 rows=13,324 loops=1)

  • Filter: ((missingdate >= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (missingdate <= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)))
  • Rows Removed by Filter: 57,255
116. 26.648 26.648 ↓ 0.0 0 13,324

Index Only Scan using xpkeinstufung on tblppreinstufung tppr (cost=0.43..4.32 rows=1 width=15) (actual time=0.002..0.002 rows=0 loops=13,324)

  • Index Cond: ((fallid = tbw.fallid) AND (datum >= trunc(tbw.missingdate)) AND (datum <= (trunc(tbw.missingdate) + 0.99998842592592592593)))
  • Heap Fetches: 19
117. 39.795 39.795 ↑ 1.0 1 13,265

Index Scan using xpkfall on tblfall tf (cost=0.42..8.26 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=13,265)

  • Index Cond: (fallid = tbw.fallid)
  • Filter: (stornierer IS NULL)
118. 39.795 39.795 ↑ 1.0 1 13,265

Index Scan using xpkpatient2 on tblpatient tp (cost=0.42..0.80 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=13,265)

  • Index Cond: (patientenid = tf.patientenid)
119. 0.130 74.767 ↓ 58.0 58 1

Nested Loop (cost=1.27..1,279.32 rows=1 width=216) (actual time=0.084..74.767 rows=58 loops=1)

120. 0.029 74.521 ↓ 58.0 58 1

Nested Loop (cost=0.85..1,278.51 rows=1 width=168) (actual time=0.060..74.521 rows=58 loops=1)

121. 15.735 74.318 ↓ 58.0 58 1

Nested Loop Semi Join (cost=0.43..1,270.25 rows=1 width=145) (actual time=0.042..74.318 rows=58 loops=1)

122. 45.259 45.259 ↓ 149.7 13,324 1

CTE Scan on bewegdata tbw_1 (cost=0.00..533.52 rows=89 width=138) (actual time=0.015..45.259 rows=13,324 loops=1)

  • Filter: ((missingdate >= to_date('2020-06-01'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)) AND (missingdate <= to_date('2020-06-25'::character varying, 'YYYY-MM-DD hh24:mi:ss'::character varying)))
  • Rows Removed by Filter: 57,255
123. 13.324 13.324 ↓ 0.0 0 13,324

Index Scan using xpkeinstufung on tblppreinstufung tppr_1 (cost=0.43..8.28 rows=1 width=15) (actual time=0.001..0.001 rows=0 loops=13,324)

  • Index Cond: ((fallid = tbw_1.fallid) AND (datum >= trunc(tbw_1.missingdate)) AND (datum <= (trunc(tbw_1.missingdate) + 0.99998842592592592593)))
  • Filter: (((allgemeinepflege IS NULL) OR (speziellepflege IS NULL)) AND (basicvalue <> '110'::numeric) AND ((isadmission)::text = 'J'::text))
  • Rows Removed by Filter: 0
124. 0.174 0.174 ↑ 1.0 1 58

Index Scan using xpkfall on tblfall tf_1 (cost=0.42..8.26 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=58)

  • Index Cond: (fallid = tbw_1.fallid)
  • Filter: (stornierer IS NULL)
125. 0.116 0.116 ↑ 1.0 1 58

Index Scan using xpkpatient2 on tblpatient tp_1 (cost=0.42..0.80 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=58)

  • Index Cond: (patientenid = tf_1.patientenid)
Execution time : 9,843.904 ms