explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pFhz

Settings
# exclusive inclusive rows x rows loops node
1. 29.175 9,455.498 ↓ 108.9 13,824 1

Sort (cost=157,314.12..157,314.44 rows=127 width=216) (actual time=9,454.067..9,455.498 rows=13,824 loops=1)

  • Sort Key: tbw.fallid, tbw.startdate, tbw.missingdate
  • Sort Method: quicksort Memory: 4,038kB
  • Functions: 375
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 40.386 ms, Inlining 0.000 ms, Optimization 14.757 ms, Emission 220.976 ms, Total 276.119 ms
2.          

CTE bewegdata

3. 41.836 9,053.089 ↓ 2.6 72,043 1

Subquery Scan on abc (cost=13,872.30..152,882.62 rows=27,219 width=39) (actual time=274.781..9,053.089 rows=72,043 loops=1)

4. 823.178 9,011.253 ↓ 2.6 72,043 1

Nested Loop (cost=13,872.30..152,474.34 rows=27,219 width=79) (actual time=274.775..9,011.253 rows=72,043 loops=1)

  • Join Filter: ((((rownum - 1)))::numeric <= (SubPlan 1))
  • Rows Removed by Join Filter: 157,277
5. 49.717 7,426.587 ↑ 3.8 364 1

Result (cost=7,334.45..53,327.47 rows=1,384 width=8) (actual time=254.874..7,426.587 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.640 7,376.870 ↓ 2.0 2,826 1

Append (cost=7,334.45..53,310.17 rows=1,384 width=0) (actual time=254.842..7,376.870 rows=2,826 loops=1)

7. 559.000 952.030 ↓ 3.5 364 1

Subquery Scan on *SELECT* 1 (cost=7,334.45..9,531.75 rows=103 width=0) (actual time=254.841..952.030 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,355
8. 35.621 393.030 ↓ 105.5 32,719 1

Nested Loop (cost=7,334.45..9,446.50 rows=310 width=192) (actual time=254.727..393.030 rows=32,719 loops=1)

9. 7.121 291.971 ↓ 59.5 32,719 1

Hash Join (cost=7,334.03..8,855.97 rows=550 width=4) (actual time=254.703..291.971 rows=32,719 loops=1)

  • Hash Cond: (c.relnamespace = n.oid)
10. 23.626 58.880 ↓ 19.8 32,853 1

Hash Join (cost=7,275.16..8,792.73 rows=1,656 width=8) (actual time=28.651..58.880 rows=32,853 loops=1)

  • Hash Cond: (x.indrelid = c.oid)
11. 6.655 6.655 ↑ 1.0 41,223 1

Seq Scan on pg_index x (cost=0.00..1,409.32 rows=41,232 width=8) (actual time=0.008..6.655 rows=41,223 loops=1)

12. 1.316 28.599 ↓ 3.0 8,735 1

Hash (cost=7,238.53..7,238.53 rows=2,930 width=8) (actual time=28.599..28.599 rows=8,735 loops=1)

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 470kB
13. 27.283 27.283 ↓ 3.0 8,735 1

Seq Scan on pg_class c (cost=0.00..7,238.53 rows=2,930 width=8) (actual time=0.017..27.283 rows=8,735 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,246
14. 0.016 225.970 ↑ 4.1 71 1

Hash (cost=55.23..55.23 rows=291 width=4) (actual time=225.970..225.970 rows=71 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
15. 225.954 225.954 ↑ 4.1 71 1

Seq Scan on pg_namespace n (cost=0.00..55.23 rows=291 width=4) (actual time=225.573..225.954 rows=71 loops=1)

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

Index Scan using pg_class_oid_index on pg_class i (cost=0.42..1.07 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=32,719)

  • Index Cond: (oid = x.indexrelid)
  • Filter: (relkind = ANY ('{i,I}'::char[]))
17. 332.354 5,900.819 ↑ 1.7 364 1

Subquery Scan on all_sequences (cost=58.87..11,157.42 rows=603 width=0) (actual time=1.629..5,900.819 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,493
18. 5,536.455 5,568.465 ↓ 5.4 9,857 1

Hash Join (cost=58.87..10,659.95 rows=1,809 width=320) (actual time=1.550..5,568.465 rows=9,857 loops=1)

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

Seq Scan on pg_class c_1 (cost=0.00..7,420.96 rows=5,447 width=8) (actual time=0.025..31.595 rows=9,857 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,124
20. 0.011 0.415 ↑ 4.1 71 1

Hash (cost=55.23..55.23 rows=291 width=4) (actual time=0.415..0.415 rows=71 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
21. 0.404 0.404 ↑ 4.1 71 1

Seq Scan on pg_namespace n_1 (cost=0.00..55.23 rows=291 width=4) (actual time=0.017..0.404 rows=71 loops=1)

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

Subquery Scan on *SELECT* 3 (cost=58.87..7,572.71 rows=324 width=0) (actual time=0.495..178.377 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,297
23. 3.129 34.810 ↓ 8.9 8,661 1

Hash Join (cost=58.87..7,305.13 rows=973 width=184) (actual time=0.447..34.810 rows=8,661 loops=1)

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

Seq Scan on pg_class c_2 (cost=0.00..7,238.53 rows=2,930 width=8) (actual time=0.043..31.290 rows=8,735 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,246
25. 0.010 0.391 ↑ 4.1 71 1

Hash (cost=55.23..55.23 rows=291 width=4) (actual time=0.391..0.391 rows=71 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
26. 0.381 0.381 ↑ 4.1 71 1

Seq Scan on pg_namespace n_2 (cost=0.00..55.23 rows=291 width=4) (actual time=0.023..0.381 rows=71 loops=1)

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

Subquery Scan on *SELECT* 4 (cost=0.28..9.60 rows=1 width=0) (actual time=0.130..0.131 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.021 0.076 ↑ 1.0 1 1

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

29. 0.030 0.030 ↑ 1.0 1 1

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

30. 0.025 0.025 ↑ 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.025..0.025 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. 72.533 166.558 ↓ 2.1 364 1

Subquery Scan on *SELECT* 5 (cost=58.87..7,445.11 rows=174 width=0) (actual time=0.642..166.558 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,155
32. 1.636 94.025 ↓ 8.7 4,519 1

Hash Join (cost=58.87..7,301.56 rows=522 width=192) (actual time=0.596..94.025 rows=4,519 loops=1)

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

Seq Scan on pg_class c_3 (cost=0.00..7,238.53 rows=1,572 width=4) (actual time=0.160..91.988 rows=4,637 loops=1)

  • Filter: (has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) AND (relkind = 'v'::char))
  • Rows Removed by Filter: 68,344
34. 0.011 0.401 ↑ 4.1 71 1

Hash (cost=55.23..55.23 rows=291 width=4) (actual time=0.401..0.401 rows=71 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
35. 0.390 0.390 ↑ 4.1 71 1

Seq Scan on pg_namespace n_3 (cost=0.00..55.23 rows=291 width=4) (actual time=0.016..0.390 rows=71 loops=1)

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

Subquery Scan on *SELECT* 6 (cost=60.64..1,432.62 rows=31 width=0) (actual time=1.864..19.412 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: 290
37. 0.116 8.294 ↓ 7.0 654 1

Nested Loop (cost=60.64..1,407.04 rows=93 width=192) (actual time=1.801..8.294 rows=654 loops=1)

38. 0.443 6.870 ↓ 7.0 654 1

Nested Loop (cost=60.35..916.93 rows=93 width=4) (actual time=1.773..6.870 rows=654 loops=1)

  • Join Filter: (pr.prolang = ln.oid)
  • Rows Removed by Join Filter: 1,962
39. 0.370 6.427 ↓ 7.0 654 1

Hash Join (cost=60.35..909.84 rows=93 width=8) (actual time=1.734..6.427 rows=654 loops=1)

  • Hash Cond: (pr.pronamespace = ns.oid)
40. 5.573 5.573 ↓ 3.0 1,053 1

Seq Scan on pg_proc pr (cost=0.00..848.58 rows=350 width=12) (actual time=1.210..5.573 rows=1,053 loops=1)

  • Filter: (has_function_privilege(oid, 'EXECUTE'::text) AND (protype = '1'::char))
  • Rows Removed by Filter: 4,658
41. 0.006 0.484 ↑ 46.8 5 1

Hash (cost=57.42..57.42 rows=234 width=4) (actual time=0.484..0.484 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
42. 0.478 0.478 ↑ 46.8 5 1

Seq Scan on pg_namespace ns (cost=0.00..57.42 rows=234 width=4) (actual time=0.031..0.478 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: 871
43. 0.000 0.000 ↑ 1.2 4 654

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

44. 0.029 0.029 ↑ 1.2 4 1

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

45. 1.308 1.308 ↑ 1.0 1 654

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

  • Index Cond: (oid = pr.prorettype)
  • Filter: (typname <> 'trigger'::name)
46. 6.059 13.215 ↓ 3.2 364 1

Subquery Scan on *SELECT* 7 (cost=60.64..2,151.79 rows=112 width=0) (actual time=2.167..13.215 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: 5
47. 0.214 7.156 ↓ 1.1 369 1

Nested Loop (cost=60.64..2,059.67 rows=335 width=192) (actual time=2.108..7.156 rows=369 loops=1)

  • Join Filter: (pr_1.prolang = ln_1.oid)
  • Rows Removed by Join Filter: 789
48. 0.135 6.942 ↓ 1.1 369 1

Nested Loop (cost=60.64..2,036.83 rows=335 width=4) (actual time=2.080..6.942 rows=369 loops=1)

49. 0.382 6.065 ↓ 1.1 371 1

Hash Join (cost=60.35..912.23 rows=335 width=8) (actual time=1.787..6.065 rows=371 loops=1)

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

Seq Scan on pg_proc pr_1 (cost=0.00..848.58 rows=1,255 width=12) (actual time=0.028..5.233 rows=3,723 loops=1)

  • Filter: (has_function_privilege(oid, 'EXECUTE'::text) AND (protype = '0'::char))
  • Rows Removed by Filter: 1,988
51. 0.006 0.450 ↑ 46.8 5 1

Hash (cost=57.42..57.42 rows=234 width=4) (actual time=0.450..0.450 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 0.444 0.444 ↑ 46.8 5 1

Seq Scan on pg_namespace ns_1 (cost=0.00..57.42 rows=234 width=4) (actual time=0.024..0.444 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: 871
53. 0.742 0.742 ↑ 1.0 1 371

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

  • Index Cond: (oid = pr_1.prorettype)
  • Filter: (typname <> 'trigger'::name)
  • Rows Removed by Filter: 0
54. 0.000 0.000 ↑ 1.7 3 369

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

55. 0.011 0.011 ↑ 1.0 5 1

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

56. 3.081 3.567 ↓ 24.7 148 1

Subquery Scan on *SELECT* 8 (cost=58.50..69.11 rows=6 width=0) (actual time=0.396..3.567 rows=148 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.065 0.486 ↓ 7.8 148 1

Merge Join (cost=58.50..63.88 rows=19 width=192) (actual time=0.343..0.486 rows=148 loops=1)

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

Index Scan using pg_namespace_oid_index on pg_namespace ns_2 (cost=0.28..114.60 rows=291 width=4) (actual time=0.035..0.096 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: 36
59. 0.050 0.325 ↓ 2.6 148 1

Sort (cost=57.99..58.13 rows=57 width=4) (actual time=0.298..0.325 rows=148 loops=1)

  • Sort Key: pg_namespace_2.nspparent
  • Sort Method: quicksort Memory: 31kB
60. 0.275 0.275 ↓ 2.6 148 1

Seq Scan on pg_namespace pg_namespace_2 (cost=0.00..56.33 rows=57 width=4) (actual time=0.012..0.275 rows=148 loops=1)

  • Filter: ((NOT nspcompoundtrigger) AND (nspparent <> '0'::oid) AND has_package_privilege(oid, 'EXECUTE'::text) AND (nspobjecttype = '0'::oid))
  • Rows Removed by Filter: 728
61. 1.322 18.954 ↓ 13.7 82 1

Subquery Scan on *SELECT* 9 (cost=60.65..71.26 rows=6 width=0) (actual time=17.661..18.954 rows=82 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.047 17.632 ↓ 4.3 82 1

Merge Join (cost=60.65..66.03 rows=19 width=192) (actual time=17.554..17.632 rows=82 loops=1)

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

Index Scan using pg_namespace_oid_index on pg_namespace ns_3 (cost=0.28..114.60 rows=291 width=4) (actual time=0.040..0.084 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: 36
64. 0.039 17.501 ↓ 1.5 82 1

Sort (cost=60.15..60.29 rows=56 width=4) (actual time=17.490..17.501 rows=82 loops=1)

  • Sort Key: pg_namespace_3.nspparent
  • Sort Method: quicksort Memory: 28kB
65. 17.462 17.462 ↓ 1.5 82 1

Seq Scan on pg_namespace pg_namespace_3 (cost=0.00..58.52 rows=56 width=4) (actual time=0.262..17.462 rows=82 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: 794
66. 13.511 16.836 ↓ 16.5 364 1

Subquery Scan on *SELECT* 10 (cost=5,535.37..5,573.46 rows=22 width=0) (actual time=3.032..16.836 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: 530
67. 0.263 3.325 ↓ 13.3 894 1

Merge Join (cost=5,535.37..5,555.03 rows=67 width=192) (actual time=2.955..3.325 rows=894 loops=1)

  • Merge Cond: (n_4.oid = c_4.relnamespace)
68. 0.069 0.069 ↑ 32.3 6 1

Index Scan using pg_namespace_oid_index on pg_namespace n_4 (cost=0.28..116.79 rows=194 width=4) (actual time=0.019..0.069 rows=6 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: 36
69. 0.217 2.993 ↓ 3.0 894 1

Sort (cost=5,535.09..5,535.84 rows=301 width=4) (actual time=2.878..2.993 rows=894 loops=1)

  • Sort Key: c_4.relnamespace
  • Sort Method: quicksort Memory: 66kB
70. 0.630 2.776 ↓ 3.0 894 1

Nested Loop (cost=0.70..5,522.70 rows=301 width=4) (actual time=0.097..2.776 rows=894 loops=1)

71. 0.330 0.330 ↓ 1.0 908 1

Index Scan using pg_trigger_tgconstraint_index on pg_trigger t (cost=0.29..108.07 rows=902 width=4) (actual time=0.025..0.330 rows=908 loops=1)

  • Index Cond: (tgconstraint = '0'::oid)
  • Filter: (((tgtype)::integer & 60) <> 0)
72. 1.816 1.816 ↑ 1.0 1 908

Index Scan using pg_class_oid_index on pg_class c_4 (cost=0.42..6.00 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=908)

  • Index Cond: (oid = t.tgrelid)
  • Filter: has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
  • Rows Removed by Filter: 0
73. 1.222 100.189 ↓ 45.0 45 1

Subquery Scan on *SELECT* 11 (cost=114.22..7,451.17 rows=1 width=0) (actual time=5.341..100.189 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)))
74. 21.772 98.967 ↓ 15.0 45 1

Nested Loop Left Join (cost=114.22..7,450.34 rows=3 width=192) (actual time=5.256..98.967 rows=45 loops=1)

  • Filter: ((c_5.relkind IS NULL) OR (c_5.relkind = 'c'::char))
  • Rows Removed by Filter: 23,125
75. 4.449 54.025 ↓ 11.7 23,170 1

Hash Join (cost=113.80..2,272.93 rows=1,983 width=4) (actual time=5.237..54.025 rows=23,170 loops=1)

  • Hash Cond: (t_1.typnamespace = n_5.oid)
76. 44.827 44.827 ↓ 2.7 31,964 1

Seq Scan on pg_type t_1 (cost=0.00..2,127.52 rows=11,983 width=8) (actual time=0.047..44.827 rows=31,964 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,541
77. 0.008 4.749 ↑ 8.1 18 1

Hash (cost=111.99..111.99 rows=145 width=4) (actual time=4.749..4.749 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
78. 1.059 4.741 ↑ 8.1 18 1

Seq Scan on pg_namespace n_5 (cost=54.56..111.99 rows=145 width=4) (actual time=4.408..4.741 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: 858
79.          

SubPlan (for Seq Scan)

80. 3.682 3.682 ↑ 1.1 156 1

Seq Scan on pg_namespace (cost=0.00..54.14 rows=170 width=64) (actual time=3.516..3.682 rows=156 loops=1)

  • Filter: ((NOT nspcompoundtrigger) AND (nspparent <> '0'::oid) AND (nspobjecttype = '0'::oid))
  • Rows Removed by Filter: 720
81. 23.170 23.170 ↑ 1.0 1 23,170

Index Scan using pg_class_oid_index on pg_class c_5 (cost=0.42..2.60 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=23,170)

  • Index Cond: (oid = t_1.typrelid)
82. 0.134 6.142 ↓ 2.0 2 1

Subquery Scan on *SELECT* 12 (cost=809.06..837.27 rows=1 width=0) (actual time=6.058..6.142 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)))
83. 0.014 6.008 ↓ 2.0 2 1

Nested Loop Left Join (cost=809.06..836.99 rows=1 width=192) (actual time=5.950..6.008 rows=2 loops=1)

  • Filter: ((c_6.relkind IS NULL) OR (c_6.relkind = 'c'::char))
84. 0.012 5.978 ↑ 2.0 2 1

Merge Join (cost=808.64..826.55 rows=4 width=4) (actual time=5.930..5.978 rows=2 loops=1)

  • Merge Cond: (n_6.oid = t_2.typnamespace)
85. 1.047 5.674 ↑ 24.2 6 1

Index Scan using pg_namespace_oid_index on pg_namespace n_6 (cost=54.84..171.35 rows=145 width=4) (actual time=5.629..5.674 rows=6 loops=1)

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

SubPlan (for Index Scan)

87. 4.627 4.627 ↑ 1.1 156 1

Seq Scan on pg_namespace pg_namespace_1 (cost=0.00..54.14 rows=170 width=64) (actual time=4.432..4.627 rows=156 loops=1)

  • Filter: ((NOT nspcompoundtrigger) AND (nspparent <> '0'::oid) AND (nspobjecttype = '0'::oid))
  • Rows Removed by Filter: 720
88. 0.011 0.292 ↑ 13.0 2 1

Sort (cost=753.80..753.87 rows=26 width=8) (actual time=0.291..0.292 rows=2 loops=1)

  • Sort Key: t_2.typnamespace
  • Sort Method: quicksort Memory: 25kB
89. 0.047 0.281 ↑ 13.0 2 1

Nested Loop (cost=0.29..753.19 rows=26 width=8) (actual time=0.063..0.281 rows=2 loops=1)

90. 0.135 0.135 ↑ 1.0 99 1

Seq Scan on pg_namespace pg_namespace_4 (cost=0.00..49.76 rows=99 width=4) (actual time=0.025..0.135 rows=99 loops=1)

  • Filter: (nspbodysrc IS NOT NULL)
  • Rows Removed by Filter: 777
91. 0.099 0.099 ↓ 0.0 0 99

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

  • 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)))
92. 0.016 0.016 ↑ 1.0 1 2

Index Scan using pg_class_oid_index on pg_class c_6 (cost=0.42..2.60 rows=1 width=5) (actual time=0.008..0.008 rows=1 loops=2)

  • Index Cond: (oid = t_2.typrelid)
93. 21.411 73.528 ↓ 10.7 630 364

Materialize (cost=6,537.85..6,589.63 rows=59 width=103) (actual time=0.055..0.202 rows=630 loops=364)

94. 32.293 52.117 ↓ 10.7 630 1

Result (cost=6,537.85..6,588.74 rows=59 width=103) (actual time=19.853..52.117 rows=630 loops=1)

95. 0.504 19.824 ↓ 10.7 630 1

Sort (cost=6,537.85..6,538.00 rows=59 width=95) (actual time=19.688..19.824 rows=630 loops=1)

  • Sort Key: tb.fallid, tb.fachlicheoe, tb.pflegerischeoe
  • Sort Method: quicksort Memory: 113kB
96. 1.499 19.320 ↓ 10.7 630 1

Nested Loop (cost=109.54..6,536.12 rows=59 width=95) (actual time=5.866..19.320 rows=630 loops=1)

97. 0.020 0.020 ↑ 1.0 1 1

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

  • Filter: ((einrichtung)::text = 'Ein1'::text)
  • Rows Removed by Filter: 6
98. 0.121 17.801 ↓ 10.7 630 1

Nested Loop (cost=109.54..6,533.70 rows=59 width=47) (actual time=5.802..17.801 rows=630 loops=1)

99. 10.962 15.766 ↓ 8.7 638 1

Bitmap Heap Scan on tblbewegung tb (cost=109.27..6,448.17 rows=73 width=47) (actual time=5.758..15.766 rows=638 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,909
  • Heap Blocks: exact=598
100. 0.002 0.592 ↓ 0.0 0 1

BitmapOr (cost=74.35..74.35 rows=2,457 width=0) (actual time=0.592..0.592 rows=0 loops=1)

101. 0.349 0.349 ↓ 4.5 5,037 1

Bitmap Index Scan on xie2tblbewegung (cost=0.00..31.66 rows=1,123 width=0) (actual time=0.349..0.349 rows=5,037 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.034 0.034 ↓ 5.2 549 1

Bitmap Index Scan on xie2tblbewegung (cost=0.00..5.48 rows=105 width=0) (actual time=0.034..0.034 rows=549 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.036 0.036 ↓ 5.2 549 1

Bitmap Index Scan on xie2tblbewegung (cost=0.00..5.48 rows=105 width=0) (actual time=0.035..0.036 rows=549 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.171 0.171 ↓ 4.5 5,037 1

Bitmap Index Scan on xie2tblbewegung (cost=0.00..31.66 rows=1,123 width=0) (actual time=0.171..0.171 rows=5,037 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. 4.212 4.212 ↑ 1.0 13 1

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

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

Index Scan using xpkkatoe on katoe (cost=0.27..1.17 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=638)

  • Index Cond: ((oe)::text = (tb.pflegerischeoe)::text)
  • Filter: ((nvl(oenichtppr, 'N'::character varying))::text <> 'J'::text)
  • Rows Removed by Filter: 0
108.          

SubPlan (for Nested Loop)

109. 687.960 687.960 ↑ 1.0 1 229,320

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

110. 1.519 9,426.323 ↓ 108.9 13,824 1

Append (cost=1.27..4,427.06 rows=127 width=216) (actual time=303.841..9,426.323 rows=13,824 loops=1)

111. 30.089 9,333.622 ↓ 109.9 13,736 1

Nested Loop (cost=1.27..2,480.86 rows=125 width=216) (actual time=303.840..9,333.622 rows=13,736 loops=1)

112. 11.480 9,262.325 ↓ 109.9 13,736 1

Nested Loop (cost=0.85..2,405.85 rows=125 width=168) (actual time=303.798..9,262.325 rows=13,736 loops=1)

113. 15.513 9,209.637 ↓ 107.3 13,736 1

Nested Loop Anti Join (cost=0.43..1,363.18 rows=128 width=138) (actual time=303.761..9,209.637 rows=13,736 loops=1)

114. 9,152.619 9,152.619 ↓ 101.7 13,835 1

CTE Scan on bewegdata tbw (cost=0.00..816.57 rows=136 width=138) (actual time=303.715..9,152.619 rows=13,835 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: 58,208
115. 41.505 41.505 ↓ 0.0 0 13,835

Index Only Scan using xpkeinstufung on tblppreinstufung tppr (cost=0.43..4.22 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=13,835)

  • Index Cond: ((fallid = tbw.fallid) AND (datum >= trunc(tbw.missingdate)) AND (datum <= (trunc(tbw.missingdate) + 0.99998842592592592593)))
  • Heap Fetches: 17
116. 41.208 41.208 ↑ 1.0 1 13,736

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

  • Index Cond: (fallid = tbw.fallid)
  • Filter: (stornierer IS NULL)
117. 41.208 41.208 ↑ 1.0 1 13,736

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

  • Index Cond: (patientenid = tf.patientenid)
118. 0.189 91.182 ↓ 44.0 88 1

Nested Loop (cost=1.27..1,944.30 rows=2 width=216) (actual time=0.523..91.182 rows=88 loops=1)

119. 0.089 90.641 ↓ 44.0 88 1

Nested Loop (cost=0.85..1,943.10 rows=2 width=168) (actual time=0.498..90.641 rows=88 loops=1)

120. 7.828 90.200 ↓ 44.0 88 1

Nested Loop Semi Join (cost=0.43..1,926.81 rows=2 width=144) (actual time=0.474..90.200 rows=88 loops=1)

121. 54.702 54.702 ↓ 101.7 13,835 1

CTE Scan on bewegdata tbw_1 (cost=0.00..816.57 rows=136 width=138) (actual time=0.376..54.702 rows=13,835 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: 58,208
122. 27.670 27.670 ↓ 0.0 0 13,835

Index Scan using xpkeinstufung on tblppreinstufung tppr_1 (cost=0.43..8.16 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=13,835)

  • 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
123. 0.352 0.352 ↑ 1.0 1 88

Index Scan using xpkfall on tblfall tf_1 (cost=0.42..8.15 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=88)

  • Index Cond: (fallid = tbw_1.fallid)
  • Filter: (stornierer IS NULL)
124. 0.352 0.352 ↑ 1.0 1 88

Index Scan using xpkpatient2 on tblpatient tp_1 (cost=0.42..0.59 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=88)

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