統計信息同步
更新時間 2025-02-05 09:37:29
最近更新時間: 2025-02-05 09:37:29
分享文章
本頁介紹天翼云TeleDB數據庫統計信息同步的操作。
初始化實例
- 通過pgxc_ctl新建一個雙CN、雙DN的實例，并開啟服務。
- 通過telesql連接到CN節點。
- 執行sql “create default node group default_group with(dn01, dn02); create sharding group to group default_group;”。
創建插件
- 通過telesql連接到CN節點。
- 執行sql “create extension teledbx_core;”
- 執行telesql命令\dx，查看插件teledbx_core是否存在。
創建枚舉類型
- 通過telesql連接到CN節點。
- 執行sql“create type week as enum('Sun','Mon','Tues','Wed','Thur','Fri','Sat');”。
創建表
執行sql “CREATE TABLE basic_types_table (
id INT PRIMARY KEY,
boolean_col BOOLEAN,
smallint_col SMALLINT,
integer_col INTEGER,
bigint_col BIGINT,
real_col REAL,
double_col DOUBLE PRECISION,
numeric_col NUMERIC(10,2),
decimal_col DECIMAL(10,2),
char_col CHAR(10),
varchar_col VARCHAR(50),
text_col TEXT,
date_col DATE,
time_col TIME,
timestamp_col TIMESTAMP,
interval_col INTERVAL,
binary_col BYTEA
);”
執行sql “create table duty(
person text,
weekday week
);”
執行sql “CREATE TABLE complextest ( id serial PRIMARY KEY, complex_column complex_type );“
創建索引
執行sql “CREATE INDEX integer_index ON basic_types_table(integer_col);” 創建索引。
創建統計對象
執行sql “CREATE STATISTICS basic_types_stats ON boolean_col, integer_col, ?double_col, numeric_col, char_col, timestamp_colFROM basic_types_table;“
插入數據
1.執行sql “DO $$
DECLARE
i INT := 1;
BEGIN
WHILE i <= 1000 LOOP
INSERT INTO basic_types_table (id, boolean_col, smallint_col, integer_col, bigint_col, real_col, double_col, numeric_col, decimal_col, char_col, varchar_col, text_col, date_col, time_col, timestamp_col, interval_col, binary_col)
VALUES (
i,
CASE WHEN random() < 0.5 THEN TRUE ELSE FALSE END,
trunc(random() * 65536 - 32768)::SMALLINT,
trunc(random() * 2147483647)::INTEGER,
trunc(random() * 9223372036854775807)::BIGINT,
random() * 1000,
random() * 1000,
trunc(random() * 1000 * random() * 100) / 100,
trunc(random() * 1000 * random() * 100) / 100,
substr(md5(random()::text), 1, 10),
substr(md5(random()::text), 1, 50),
md5(random()::text),
CURRENT_DATE - (trunc(random() * 3650) || ' days')::INTERVAL,
CURRENT_TIME - (trunc(random() * 86400) || ' seconds')::INTERVAL,
CURRENT_TIMESTAMP - (trunc(random() * 3650) || ' days')::INTERVAL,
(trunc(random() * 3650) || ' days')::INTERVAL,
decode(md5(random()::text), 'hex')
);
i := i + 1;
END LOOP;
END $$;”插入數據。
2.執行sql “select count(*) from basic_types_table;”查詢表內數據行數。
3.執行sql “insert into duty values('April','Sun');
insert into duty values('Harris','Mon');
insert into duty values('Dave','Wed');”插入數據。
4.執行sql “select count(*) from duty; “查詢表內數據行數
創建復雜類型
1.使用普通用戶權限telesql連接到CN節點。
2.執行sql “CREATE OR REPLACE FUNCTION add_numbers(a FLOAT, b FLOAT)
RETURNS FLOAT AS $$
SELECT a + b;
$$ LANGUAGE SQL;“
3.執行sql“CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
SELECT a + b;
$$ LANGUAGE SQL;”
4.執行sql“CREATE OR REPLACE FUNCTION insert_complextest_with_oid(oid_value oid) RETURNS void AS $$
BEGIN
INSERT INTO complextest (complex_column) VALUES (ROW('Sun', oid_value::regproc));
END;
$$ LANGUAGE plpgsql; “
5.執行sql“SELECT insert_complextest_with_oid(oid)
FROM pg_proc
WHERE proname = 'add_numbers'; “
執行analyze
1.使用普通用戶權限telesql連接到CN節點。
2.執行sql “ANALYZE ;”生成統計信息。
3.執行sql “\o first_pg_stats_result.csv
EXECUTE DIRECT ON (coord1) 'SELECT * FROM pg_stats order by (schemaname,tablename,attname)';
\o second_pg_stats_result.csv
EXECUTE DIRECT ON (coord2) 'SELECT * FROM pg_stats order by (schemaname,tablename,attname)';
\o
\! diff first_pg_stats_result.csv second_pg_stats_result.csv“ 比較cn之間的pg_stats是否相同
4.執行sql“\o first_pg_statistic_ext_result.csv
EXECUTE DIRECT ON (coord1) 'SELECT * FROM pg_statistic_ext where stxname =''basic_types_stats''';
\o second_pg_statistic_ext_result.csv
EXECUTE DIRECT ON (coord2) 'SELECT * FROM pg_statistic_ext where stxname =''basic_types_stats''';
\o
\! diff first_pg_statistic_ext_result.csv second_pg_statistic_ext_result.csv” 比較cn之間的pg_statistic_ext 是否相同
5.執行sql“ \o first_pg_class_result.csv
EXECUTE DIRECT ON (coord1) '
SELECT relname, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relhaspkey, relhasrules, relhastriggers
FROM pg_class
WHERE relname IN (''basic_types_table'', ''integer_index'')';
\o second_pg_class_result.csv
EXECUTE DIRECT ON (coord2) '
SELECT relname, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relhaspkey, relhasrules, relhastriggers
FROM pg_class
WHERE relname IN (''basic_types_table'', ''integer_index'')';
\o
\! diff first_pg_class_result.csv second_pg_class_result.csv”比較pg_class是否相同
