Changeset: f4fd43daab84 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f4fd43daab84
Modified Files:
sql/scripts/75_storagemodel.sql
sql/test/BugTracker-2013/Tests/create_table_with_func.Bug-3286.sql
sql/test/BugTracker-2013/Tests/create_table_with_func.Bug-3286.stable.out
sql/test/BugTracker-2015/Tests/alter-table.Bug-3828-part1.stable.out
sql/test/BugTracker-2016/Tests/assert-on-push-project-up.Bug-6077.stable.out
sql/test/BugTracker-2016/Tests/storagemodel.sql
sql/test/BugTracker-2016/Tests/storagemodel.stable.out
sql/test/BugTracker-2017/Tests/crash_correlated_subqueries_in_select.Bug-6254.stable.out
sql/test/BugTracker-2017/Tests/sqlsmith.Bug-6423.stable.out
sql/test/BugTracker-2018/Tests/groupby_having_orderby_count.Bug-6624.stable.out
sql/test/Dependencies/Tests/Dependencies.stable.out
sql/test/Dependencies/Tests/Dependencies.stable.out.int128
sql/test/Dependencies/Tests/dependency_loop.stable.out
sql/test/Dependencies/Tests/dependency_loop.stable.out.int128
sql/test/Tests/systemfunctions.stable.out
sql/test/Tests/systemfunctions.stable.out.int128
sql/test/mergetables/Tests/sqlsmith-apply-outer-join-or.sql
sql/test/orderidx/Tests/simpletable.stable.out
sql/test/orderidx/Tests/simpletable.stable.out.32bit
sql/test/orderidx/Tests/smalltable.stable.out
sql/test/orderidx/Tests/smalltable.stable.out.32bit
sql/test/pg_regress/Tests/vacuum.stable.out
sql/test/pg_regress/Tests/without_oid.stable.out
sql/test/pg_regress/Tests/without_oid.stable.out.32bit
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.stable.out
Branch: default
Log Message:
Improve and extend storagemodel structures and computation.
The upgrade program and the Changelog note will be done later.
diffs (truncated from 1826 to 300 lines):
diff --git a/sql/scripts/75_storagemodel.sql b/sql/scripts/75_storagemodel.sql
--- a/sql/scripts/75_storagemodel.sql
+++ b/sql/scripts/75_storagemodel.sql
@@ -19,12 +19,12 @@
create function sys."storage"()
returns table (
- "schema" string,
- "table" string,
- "column" string,
- "type" string,
- "mode" string,
- location string,
+ "schema" varchar(1024),
+ "table" varchar(1024),
+ "column" varchar(1024), -- name of column or index or pkey or fkey or
unique constraint
+ "type" varchar(1024),
+ "mode" varchar(15),
+ location varchar(1024),
"count" bigint,
typewidth int,
columnsize bigint,
@@ -39,17 +39,44 @@ returns table (
)
external name sql."storage";
-create view sys."storage" as select * from sys."storage"();
+create view sys."storage" as
+select * from sys."storage"()
+order by "schema", "table", "column";
+
+create view sys."tablestorage" as
+select "schema", "table",
+ max("count") as "rowcount",
+ count(*) as "storages",
+ sum(columnsize) as columnsize,
+ sum(heapsize) as heapsize,
+ sum(hashes) as hashsize,
+ sum("imprints") as imprintsize,
+ sum(orderidx) as orderidxsize
+ from sys."storage"()
+group by "schema", "table"
+order by "schema", "table";
+
+create view sys."schemastorage" as
+select "schema",
+ count(*) as "storages",
+ sum(columnsize) as columnsize,
+ sum(heapsize) as heapsize,
+ sum(hashes) as hashsize,
+ sum("imprints") as imprintsize,
+ sum(orderidx) as orderidxsize
+ from sys."storage"()
+group by "schema"
+order by "schema";
-- refinements for schemas, tables, and individual columns
-create function sys."storage"( sname string)
+create function sys."storage"(sname varchar(1024))
returns table (
- "schema" string,
- "table" string,
- "column" string,
- "type" string,
- "mode" string,
- location string,
+ "schema" varchar(1024),
+ "table" varchar(1024),
+ "column" varchar(1024),
+ "type" varchar(1024),
+ "mode" varchar(15),
+ location varchar(1024),
"count" bigint,
typewidth int,
columnsize bigint,
@@ -64,14 +91,14 @@ returns table (
)
external name sql."storage";
-create function sys."storage"( sname string, tname string)
+create function sys."storage"(sname varchar(1024), tname varchar(1024))
returns table (
- "schema" string,
- "table" string,
- "column" string,
- "type" string,
- "mode" string,
- location string,
+ "schema" varchar(1024),
+ "table" varchar(1024),
+ "column" varchar(1024),
+ "type" varchar(1024),
+ "mode" varchar(15),
+ location varchar(1024),
"count" bigint,
typewidth int,
columnsize bigint,
@@ -86,14 +113,14 @@ returns table (
)
external name sql."storage";
-create function sys."storage"( sname string, tname string, cname string)
+create function sys."storage"(sname varchar(1024), tname varchar(1024), cname
varchar(1024))
returns table (
- "schema" string,
- "table" string,
- "column" string,
- "type" string,
- "mode" string,
- location string,
+ "schema" varchar(1024),
+ "table" varchar(1024),
+ "column" varchar(1024),
+ "type" varchar(1024),
+ "mode" varchar(15),
+ location varchar(1024),
"count" bigint,
typewidth int,
columnsize bigint,
@@ -108,154 +135,185 @@ returns table (
)
external name sql."storage";
+
-- To determine the footprint of an arbitrary database, we first have
-- to define its schema, followed by an indication of the properties of each
column.
--- A storage model input table for the size prediction is shown below:
+-- A storage model input table for the size prediction is shown below.
+-- This table can be adjusted to reflect the anticipated final database size.
create table sys.storagemodelinput(
- "schema" string,
- "table" string,
- "column" string,
- "type" string,
- "typewidth" int,
- "count" bigint, -- estimated number of tuples
- "distinct" bigint, -- indication of distinct number of strings
- "atomwidth" int, -- average width of strings or clob
- "reference" boolean, -- used as foreign key reference
- "sorted" boolean, -- if set there is no need for an index
+ "schema" varchar(1024) NOT NULL,
+ "table" varchar(1024) NOT NULL,
+ "column" varchar(1024) NOT NULL, -- name of column or index or
pkey or fkey or unique constraint
+ "type" varchar(1024) NOT NULL,
+ typewidth int NOT NULL,
+ "count" bigint NOT NULL, -- estimated number of tuples
+ "distinct" bigint NOT NULL, -- indication of distinct number of
strings
+ atomwidth int NOT NULL, -- average width of variable size char
or binary strings
+ reference boolean NOT NULL, -- used as foreign key reference
+ sorted boolean, -- if set there is no need for an index
revsorted boolean,
"unique" boolean,
- "orderidx" bigint -- an ordered oid index
+ orderidxsize bigint NOT NULL -- an ordered oid index
);
--- this table can be adjusted to reflect the anticipated final database size
--- The model input can be derived from the current database using
+-- The model input can be derived from the current database using
intitalisation procedure:
create procedure sys.storagemodelinit()
begin
delete from sys.storagemodelinput;
insert into sys.storagemodelinput
- select X."schema", X."table", X."column", X."type", X.typewidth,
X.count, 0, X.typewidth, false, X.sorted, X.revsorted, X."unique", X.orderidx
from sys."storage"() X;
+ select "schema", "table", "column", "type", typewidth, "count", 0,
typewidth, FALSE, sorted, revsorted, "unique", orderidx
+ from sys."storage"()
+ -- exclude system tables (those are not useful to be modeled for
storagesize for application users)
+ where ("schema", "table") in (
+ SELECT sch."name", tbl."name"
+ FROM sys."_tables" AS tbl JOIN sys."schemas" AS sch ON
tbl.schema_id = sch.id
+ WHERE tbl."system" = FALSE)
+ order by "schema", "table", "column";
update sys.storagemodelinput
- set reference = true
- where concat(concat("schema","table"), "column") in (
- SELECT concat( concat("fkschema"."name", "fktable"."name"),
"fkkeycol"."name" )
- FROM "sys"."keys" AS "fkkey",
- "sys"."objects" AS "fkkeycol",
- "sys"."tables" AS "fktable",
- "sys"."schemas" AS "fkschema"
- WHERE "fktable"."id" = "fkkey"."table_id"
- AND "fkkey"."id" = "fkkeycol"."id"
- AND "fkschema"."id" = "fktable"."schema_id"
- AND "fkkey"."rkey" > -1);
+ set "distinct" = "count"
+ where "unique" = TRUE
+ or "type" IN ('varchar', 'char', 'clob', 'blob', 'json', 'url'); --
assume all strings are distinct
update sys.storagemodelinput
- set "distinct" = "count" -- assume all distinct
- where "type" = 'varchar' or "type"='clob';
+ set reference = TRUE
+ where ("schema", "table", "column") in (
+ SELECT fkschema."name", fktable."name", fkkeycol."name"
+ FROM sys."keys" AS fkkey,
+ sys."objects" AS fkkeycol,
+ sys."tables" AS fktable,
+ sys."schemas" AS fkschema
+ WHERE fktable."id" = fkkey."table_id"
+ AND fkkey."id" = fkkeycol."id"
+ AND fkschema."id" = fktable."schema_id"
+ AND fkkey."rkey" > -1 );
end;
+
-- The predicted storage footprint of the complete database
-- determines the amount of diskspace needed for persistent storage
-- and the upperbound when all possible index structures are created.
-- The storage requirement for foreign key joins is split amongst the
participants.
-create function sys.columnsize(nme string, i bigint, d bigint)
+create function sys.columnsize(tpe varchar(1024), count bigint, _distinct
bigint, avgwidth int)
returns bigint
begin
- case
- when nme = 'boolean' then return i;
- when nme = 'char' then return 2*i;
- when nme = 'smallint' then return 2 * i;
- when nme = 'int' then return 4 * i;
- when nme = 'bigint' then return 8 * i;
- when nme = 'hugeint' then return 16 * i;
- when nme = 'timestamp' then return 8 * i;
- when nme = 'varchar' then
- case
- when cast(d as bigint) << 8 then return i;
- when cast(d as bigint) << 16 then return 2 * i;
- when cast(d as bigint) << 32 then return 4 * i;
- else return 8 * i;
- end case;
- else return 8 * i;
- end case;
+ -- for fixed size types: typewidth_inbytes * count
+ if tpe = 'tinyint' or tpe = 'boolean'
+ then
+ return count;
+ end if;
+ if tpe = 'smallint'
+ then
+ return 2 * count;
+ end if;
+ if tpe = 'int' or tpe = 'real' or tpe = 'date' or tpe = 'time' or tpe =
'timetz' or tpe = 'sec_interval' or tpe = 'month_interval'
+ then
+ return 4 * count;
+ end if;
+ if tpe = 'bigint' or tpe = 'decimal' or tpe = 'double' or tpe =
'timestamp' or tpe = 'timestamptz' or tpe = 'inet' or tpe = 'oid'
+ then
+ return 8 * count;
+ end if;
+ if tpe = 'hugeint' or tpe = 'uuid'
+ then
+ return 16 * count;
+ end if;
+
+ -- for variable size types it is more complicated
+ if tpe = 'varchar' or tpe = 'char' or tpe = 'clob' or tpe = 'json' or
tpe = 'url'
+ then
+ return sys.sql_max(4 * count, 8192 + ((avgwidth + 8) *
_distinct));
+ end if;
+ if tpe = 'blob'
+ then
+ return (avgwidth + 8) * count;
+ end if;
+
+ return 16 * count;
end;
-create function sys.heapsize(tpe string, i bigint, w int)
+create function sys.heapsize(tpe varchar(1024), count bigint, _distinct
bigint, avgwidth int)
returns bigint
begin
- if tpe <> 'varchar' and tpe <> 'clob'
+ if tpe = 'varchar' or tpe = 'char' or tpe = 'clob' or tpe = 'json' or
tpe = 'url'
then
- return 0;
+ return 8192 + ((avgwidth + 8) * _distinct);
end if;
- return 10240 + i * w;
-end;
-
-create function sys.hashsize(b boolean, i bigint)
-returns bigint
-begin
- -- assume non-compound keys
- if b = true
+ if tpe = 'blob'
then
- return 8 * i;
+ return (avgwidth + 8) * count;
end if;
return 0;
end;
-create function sys.imprintsize(i bigint, nme string)
+create function sys.hashsize(b boolean, count bigint)
returns bigint
begin
- if nme = 'boolean'
- or nme = 'tinyint'
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
