云数据库 GAUSSDB-案例：调整基于代价的查询重写GUC参数costbased_rewrite_rule:案例环境准备

时间：2024-11-13 14:46:24

云数据库 GAUSSDB 实际调优案例

案例环境准备

为了便于规则的使用场景演示，需准备建表语句如下：

SET client_min_messages = warning;
SET CLIENT_ENCODING = 'UTF8';
--清理环境。
DROP SCHEMA IF EXISTS costbased_rule_test cascade;
CREATE SCHEMA costbased_rule_test;
SET current_schema = costbased_rule_test;
SET enable_codegen = off;
DROP TABLE IF EXISTS costbased_rule_test.ct1;
DROP TABLE IF EXISTS costbased_rule_test.ct2;
DROP TABLE IF EXISTS costbased_rule_test.ct3;
DROP TABLE IF EXISTS costbased_rule_test.ct4;
--创建测试表。
CREATE TABLE ct1 (a INT, b INT, c INT, d INT);
CREATE TABLE ct2 (a INT, b INT, c INT, d INT);
CREATE TABLE ct3 (a INT, b INT, c INT, d INT);
CREATE TABLE ct4 (a INT, b INT, c INT, d INT);
CREATE INDEX idx_ct1_b ON ct1(b);
CREATE INDEX idx_ct2_c ON ct2(c);
CREATE INDEX idx_ct3_c ON ct3(c);
--插入数据。
INSERT INTO ct1 (a, b, c) VALUES (generate_series(1, 100), generate_series(200, 300), left(random()::int, 100));
INSERT INTO ct2 VALUES(1,2,3,4),(3,4,5,6);
INSERT INTO ct3 (a, b, c, d) VALUES (generate_series(1, 10), generate_series(20, 30), left(random()::int, 10), left(random()::int, 10));
--更新统计信息。
ANALYZE ct1;
ANALYZE ct2;
ANALYZE ct3;

上一篇：云数据库 GAUSSDB-PG_STATIO_SYS_TABLES

下一篇：云数据库 GAUSSDB-pl_get_global_trace_info