详解GaussDB(DWS)中3个防过载检查项
1.华为云峰会2024,GaussDB扬帆出海,给世界一个更优选择2.预算有限,资源冗余?DWS集群缩容如何帮你解决烦劳3.数智融合,华为云GaussDB(for MySQL)助力企业释放数据新价值4.RDS for MySQL Serverless公测上线:弹性伸缩,最高可降成本超80%5.GaussDB跨云容灾:实现跨地域的数据库高可用能力6.实例详解如何构建动态SQL语句7.GaussDB(DWS)运维利刃:TopSQL工具解析8.手把手带你认识GaussDB轻量化运维管理工具9.守护更多女性健康,华为云GeminiDB助力美柚数据库高效稳定迁移10.GaussDB(DWS)集群通信:详解pooler连接池11.Libcomm通信库:GaussDB(DWS) 为解决建联过多的小妙招12.实例带你了解GaussDB数据库的LOCK TABLE13.RDS for MariaDB“智能DBA助手”,让运维效率嗖嗖地!14.实例带你了解GaussDB的索引管理
15.详解GaussDB(DWS)中3个防过载检查项
16.华为云数据库创新发展论坛,打造行业更优数据库底座!17.GaussDB(分布式)实例故障处理18.华为云GeminiDB新版本发布:全面支持Redis 6.219.究竟什么样的数据库,才能承接RTA广告这个技术活!20.GaussDB(DWS) 业务高可靠的三大利器:CN RETRY、远程读、ELB21.走在前、做示范,苏州农商银行携华为云完成超级网银系统改造22.分布式数据库技术的演进和发展方向23.新版Redis不再“开源”,对使用者都有哪些影响?24.cgroup、资源池、用户的关系..涉及到GaussDB(DWS)的资源设置25.GeminiDB Cassandra接口新特性FLASHBACK发布:任意时间点秒级闪回26.探索GaussDB(DWS)湖仓融合:Hudi与元数据打通的深度解析27.详解数仓对象设计中序列SEQUENCE原理与应用28.数仓调优实战:GUC参数调优29.详讲openGauss 5.0 单点企业版如何部署_Centos7_x8630.华为云GeminiDB,广告RTA的“登云梯”31.DTC2024,华为云数据库创新融合大发展,打造世界级数据库!32.GaussDB(DWS)基于Flink的实时数仓构建33.数仓的两种轻量级数据交换格式:json与jsonb34.重磅新品发布!云耀数据库HRDS,享受轻量级的极致体验35.“企业创新新引擎”数据库专项赋能会,让云原生技术普惠千行百业!36.GaussDB SQL查询语句执行过程解析37.详解数仓的向量化执行引擎38.Redis开源社区持续壮大,华为云为Valkey项目注入新的活力39.详解数仓的3A安全能力40.【GaussDB(for MySQL)】 Big IN查询优化41.GaussDB细粒度资源管控技术透视42.带你了解GaussDB SQL中的BOOLEAN表达式43.数仓安全:数据脱敏技术深度解析44.详解GaussDB(DWS)中的行执行引擎45.了解GaussDB SQL中CASE表达式46.JDBC连接openGauss6.0和PostgreSQL16.2性能对比47.MySQL 给用户添加 ALTER VIEW 的权限48.MySQL全文索引源码剖析之Insert语句执行过程49.全球厂商之最,华为17篇论文入选国际数据库顶会ICDE50.GeminiDB PITR,让游戏回档“进退自如”!51.浅析MySQL 8.0直方图原理52.LLVM技术在GaussDB等数据库中的应用53.告别内存OOM,解决MySQL内存增长问题54.从数据库设计到性能调优,全面掌握openGemini应用开发最佳实践55.深度体验与测评openGauss 6.0.0新版本56.深度解读数据库引入LLVM技术后如何提升性能57.从Purge机制说起,详解GaussDB(for MySQL)的优化策略58.攀登不止,华为数据库论文入选SIGMOD 2024,技术创新再谱新篇59.技术解读数据库如何实现“多租户”?60.解读MySQL 8.0数据字典的初始化与启动61.GeminiDB全面联动MySQL:热点数据,一键加速62.探秘数据库中的并行计算技术应用63.硬核解读,WeTune是如何提升数据库查询重写性能?64.开源数据库Greenplu突然闭源?GaussDB(DWS)提供数仓新可能65.数据库异常难定位?GaussDB(DWS)运维神器TopSQL来解决66.MySQL派生表合并优化的原理和实现67.华为云发起,openGemini正式成为CNCF官方项目!68.MySQL中为什么要使用索引合并(Index Merge)?69.解读MySQL 8.0数据字典缓存管理机制70.解读GaussDB(for MySQL)灵活多维的二级分区表策略71.深度解读GaussDB(for MySQL)与MySQL的COUNT查询并行优化策略72.一图为你揭秘云数据库GaussDB管理平台亮点73.深度解读GaussDB逻辑解码技术原理74.深度解读RDS for MySQL 审计日志功能和原理75.遇到慢查询怎么办?一文解读MySQL 8.0查询分析工具76.从源码分析,MySQL优化器如何估算SQL语句的访问行数77.一文带你搞懂GaussDB数据库性能调优78.揭秘UGO SQL审核功能4大特性,让业务平滑迁移至GaussDB79.了解GaussDB性能调优之隐式转换,解决慢SQL问题80.全面解读TaurusDB透明压缩特性,降低数据库使用成本81.解读GaussDB的BTree索引和UBTree索引,如何带来更强并发能力82.想提高查询性能,用GaussDB(DWS) in表达式还是or表达式?83.详解GaussDB(DWS)逻辑集群,如何化解大规模业务数据管理难题84.TaurusDB库表时间点极速恢复,大幅缩短数据恢复时间85.拦截烂SQL,解读GaussDB(DWS)查询过滤器过滤规则原理86.开源for Huawei,Beam适配GaussDB实践案例分享87.优化大宽表查询性能,揭秘GaussDB(DWS) 谓词列analyze本文分享自华为云社区《【防过载检查项】》,作者: 譡里个檔。
1. GUC参数检查
目的:针对不同版本建议设定不同的参数值,当前先检查出来,后续diagnosis会给出建议值
SELECT split_part((substring(version() from '\((.*)\)')), ' ', 2) AS version, (EXISTS (SELECT 1 FROM (SELECT count(DISTINCT node_name) AS dn_cnt FROM pgxc_node WHERE node_type = 'D' AND node_host <> 'localhost' GROUP BY node_host) WHERE dn_cnt <> 1) ) AS sdn_per_node, node_name, name AS guc_name, CASE WHEN unit = 'ms' THEN setting::bigint/1000 || ' s' WHEN unit = 's' THEN setting || ' s' WHEN unit = 'KB' THEN pg_size_pretty(setting*1024) WHEN unit = '8KB' THEN pg_size_pretty(setting*1024*8) ELSE setting END AS setting, unit, CASE WHEN name = 'default_distribution_mode' THEN CASE WHEN setting <> 'roundrobin' THEN 'roundrobin' END WHEN name = 'autovacuum' THEN CASE WHEN setting <> 'on' THEN 'on' END WHEN name = 'autovacuum_max_workers' THEN CASE WHEN setting::int > 6 THEN '6' END WHEN name = 'autovacuum_max_workers' THEN CASE WHEN setting::int > 3 THEN '3' END WHEN name = 'session_timeout' THEN CASE WHEN setting::int > 600 OR setting::int = 0 THEN '<= 10min' END WHEN name = 'statement_timeout' THEN CASE WHEN setting::int > 24* 60 * 1000 OR setting::int = 0 THEN '<= 24h' END WHEN name = 'sql_use_spacelimit' THEN CASE WHEN setting::int > 300*1024*1024 OR setting IN ('0', '-1') THEN '<= 300GB' END WHEN name = 'temp_file_limit' THEN CASE WHEN setting::int > 100*1024*1024 OR setting IN ('0', '-1') THEN '<= 100GB' END WHEN name = 'udf_memory_limit' THEN CASE WHEN setting::int > 1*1024*1024 THEN '<= 1GB' END WHEN name = 'query_dop' THEN CASE WHEN setting::int = 0 THEN ' -4' END WHEN name = 'max_streams_per_query' THEN CASE WHEN setting::int = -1 THEN '50' END WHEN name = 'max_connections' THEN CASE WHEN node_name = 'dn_6001_6002' AND setting::int < 5000 THEN '>=5000' END ELSE '' END AS diagnosis FROM pgxc_parallel_query('all', 'SELECT pgxc_node_str() AS node_name, name, setting, unit FROM pg_settings WHERE pgxc_node_str() IN (''cn_5001'',''dn_6001_6002'') AND name in (''max_streams_per_query'',''query_dop'' ,''sql_use_spacelimit'',''temp_file_limit'',''default_distribution_mode'', ''autovacuum_mode'',''autovacuum'',''autovacuum_max_workers_hstore'',''autovacuum_max_workers'', ''session_timeout'',''statement_timeout'',''ddl_lock_timeout'',''idle_in_transaction_timeout'', ''max_connections'',''min_pool_size'',''max_pool_size'', ''max_stream_pool'',''max_active_statements'',''max_prepared_transactions'', ''cstore_buffers'',''shared_buffers'', ''max_process_memory'', ''udf_memory_limit'', ''max_process_memory_balanced'', ''bbox_dump_count'', ''enable_bbox_dump'')') AS (node_name name, name text, setting text, unit text) ORDER BY node_name, name;
2. 大表检查
目的:识别大表,建议客户整改,避免磁盘过载
8.1.3版本使用如下SQL
SELECT CASE WHEN (skewsize > avgsize * 0.10 AND skewsize > 50 * 1024) THEN 'skew table' WHEN (reloptions::text LIKE '%orientation=column%' AND reloptions::text LIKE '%compression=no%') THEN 'uncompressed column table' WHEN (x.pclocatortype = 'R' AND avgsize > 10 * 1024) THEN 'large replicattion table' WHEN (pg_stat_get_dead_tuples(c.oid) >100000 AND pg_stat_get_dead_tuples(c.oid)/(pg_stat_get_dead_tuples(c.oid)+pg_stat_get_live_tuples(c.oid)) > 0.4) THEN 'dirty table' ELSE 'normal large table' END AS diagnostic, t1.schemaname, -- 表的schema t1.tablename, -- 表名 a.rolname AS tableowner, x.pgroup AS nodegroup, CASE x.pclocatortype WHEN 'H' THEN 'Hash' WHEN 'N' THEN 'Round Robin' WHEN 'R' THEN 'Replicate' END AS locatortype, CASE WHEN c.parttype = 'p' THEN true ELSE false END AS ispartitioned, CASE WHEN reloptions::text LIKE '%orientation=column%' THEN 'column' WHEN reloptions::text LIKE '%orientation=row%' THEN 'row' END AS orientation, t1.dnnum, -- 表的node group的DN数 t1.totalsize AS "totalsize(MB)", -- 表的size ,单位MB t1.avgsize AS "avgsize(MB)", -- 平均每个DN上数据量,单位MB t1.skewsize AS "skewsize(MB)", -- 不同DN上数据size的最大差值,单位MB t1.skewdn, -- 数据量最大的DN t1.maxratio, -- 数据量最大DN的size/平均size t1.minratio, -- 数据量最小DN的size/平均size t1.skewratio -- 不同DN上数据size的最大差值/平均size FROM ( -- 预处理,识别倾斜表 SELECT schemaname, tablename, skewdn, dnnum, totalsize, avgsize, skewsize, (maxsize/avgsize)::numeric(20,2) AS maxratio, (minsize/avgsize)::numeric(20,2) AS minratio, (skewsize/avgsize)::numeric(20,2) AS skewratio FROM ( SELECT schemaname,tablename,skewdn,count(1) AS dnnum,sum(dnsize) AS totalsize, avg(dnsize) AS avgsize,max(dnsize) AS maxsize,min(dnsize) AS minsize, (max(dnsize) - min(dnsize)) AS skewsize FROM ( --对每个表的数据按照DN数据量大小排序,以及获取倾斜的dn SELECT schemaname, tablename, nodename, (dnsize/1024/1024)::bigint AS dnsize, -- 单位换算为MB first_value(nodename) over (PARTITION BY schemaname, tablename ORDER BY dnsize DESC, nodename) AS skewdn -- --数据量最大的DN FROM ( -- 获取大于10GB的表 SELECT schemaname, tablename,(rd).nodename, ((rd).dnsize + 1) AS dnsize FROM ( SELECT schemaname, tablename, gs_table_distribution(schemaname, tablename) AS rd FROM gs_table_distribution() WHERE schemaname NOT IN ('pg_catalog', 'dbms_om', 'cstore') AND relkind = 'r' GROUP BY schemaname, tablename HAVING sum(dnsize) > 50.0 * 1024 * 1024 * 1024 -- 总大小大于100GB ) ) ) GROUP BY schemaname,tablename, skewdn ) ) t1 INNER JOIN pg_class c ON c.relname = t1.tablename LEFT JOIN pg_namespace n ON n.nspname = t1.schemaname LEFT JOIN pg_authid a ON a.oid = c.relowner LEFT JOIN pgxc_class x ON x.pcrelid = c.oid WHERE c.reloptions::text NOT LIKE '%internal_mask%' ORDER BY totalsize DESC, diagnostic, skewsize DESC ;
8.2.1和8.2.0版本使用如下
-- 大表诊断 SELECT CASE WHEN (skewsize > avgsize * 0.10 AND skewsize > 50 * 1024) THEN 'skew table' WHEN (reloptions::text LIKE '%orientation=column%' AND reloptions::text LIKE '%compression=no%') THEN 'uncompressed column table' WHEN (x.pclocatortype = 'R' AND avgsize > 10 * 1024) THEN 'large replicattion table' WHEN (pg_stat_get_dead_tuples(c.oid) >100000 AND pg_stat_get_dead_tuples(c.oid)/(pg_stat_get_dead_tuples(c.oid)+pg_stat_get_live_tuples(c.oid)) > 0.4) THEN 'dirty table' WHEN (reloptions::text LIKE '%orientation=column%') THEN CASE WHEN (SELECT total_cu_count > 0 AND (zero_size_cu_count + small_cu_count)/total_cu_count > 0.5 FROM get_col_cu_info(t1.schemaname, t1.tablename)) THEN 'small cu table' ELSE 'normal large table' END ELSE 'normal large table' END AS diagnostic, t1.schemaname, -- 表的schema t1.tablename, -- 表名 a.rolname AS tableowner, x.pgroup AS nodegroup, CASE x.pclocatortype WHEN 'H' THEN 'Hash' WHEN 'N' THEN 'Round Robin' WHEN 'R' THEN 'Replicate' END AS locatortype, CASE WHEN c.parttype = 'p' THEN true ELSE false END AS ispartitioned, CASE WHEN reloptions::text LIKE '%orientation=column%' THEN 'column' WHEN reloptions::text LIKE '%orientation=row%' THEN 'row' END AS orientation, t1.dnnum, -- 表的node group的DN数 t1.totalsize AS "totalsize(MB)", -- 表的size ,单位MB t1.avgsize AS "avgsize(MB)", -- 平均每个DN上数据量,单位MB t1.skewsize AS "skewsize(MB)", -- 不同DN上数据size的最大差值,单位MB t1.skewdn, -- 数据量最大的DN t1.maxratio, -- 数据量最大DN的size/平均size t1.minratio, -- 数据量最小DN的size/平均size t1.skewratio -- 不同DN上数据size的最大差值/平均size FROM ( -- 预处理,识别倾斜表 SELECT schemaname, tablename, skewdn, dnnum, totalsize, avgsize, skewsize, (maxsize/avgsize)::numeric(20,2) AS maxratio, (minsize/avgsize)::numeric(20,2) AS minratio, (skewsize/avgsize)::numeric(20,2) AS skewratio FROM ( SELECT schemaname, tablename, skewdn, count(1) AS dnnum, sum(dnsize) AS totalsize, avg(dnsize) AS avgsize, max(dnsize) AS maxsize, min(dnsize) AS minsize, (max(dnsize) - min(dnsize)) AS skewsize FROM ( --对每个表的数据按照DN数据量大小排序,以及获取倾斜的dn SELECT schemaname, tablename, nodename, (dnsize/1024/1024)::bigint AS dnsize, -- 单位换算为MB first_value(nodename) over (PARTITION BY schemaname, tablename ORDER BY dnsize DESC, nodename) AS skewdn -- --数据量最大的DN FROM ( -- 获取大于10GB的表 SELECT schemaname, tablename,(rd).nodename, ((rd).dnsize + 1) AS dnsize FROM ( SELECT schemaname, tablename, gs_table_distribution(schemaname, tablename) AS rd FROM gs_table_distribution() WHERE schemaname NOT IN ('pg_catalog', 'dbms_om', 'cstore') AND relkind = 'r' GROUP BY schemaname, tablename HAVING sum(dnsize) > 50* 1024 * 1024 * 1024.0 -- 总大小大于100GB ) ) ) GROUP BY schemaname,tablename, skewdn ) ) t1 INNER JOIN pg_class c ON c.relname = t1.tablename LEFT JOIN pg_namespace n ON n.nspname = t1.schemaname LEFT JOIN pg_authid a ON a.oid = c.relowner LEFT JOIN pgxc_class x ON x.pcrelid = c.oid WHERE c.reloptions::text NOT LIKE '%internal_mask%' ORDER BY totalsize DESC, diagnostic, skewsize DESC ;
8.3.0版本使用
-- 大表诊断 SELECT CASE WHEN (skewsize > avgsize * 0.10 AND skewsize > 50 * 1024) THEN 'skew table' WHEN (reloptions::text LIKE '%orientation=column%' AND reloptions::text LIKE '%compression=no%') THEN 'uncompressed column table' WHEN (x.pclocatortype = 'R' AND avgsize > 10 * 1024) THEN 'large replicattion table' WHEN (pg_stat_get_dead_tuples(c.oid) >100000 AND pg_stat_get_dead_tuples(c.oid)/(pg_stat_get_dead_tuples(c.oid)+pg_stat_get_live_tuples(c.oid)) > 0.4) THEN 'dirty table' WHEN (reloptions::text LIKE '%orientation=column%') THEN CASE WHEN (SELECT total_cu_count > 0 AND (zero_cu_count + small_cu_count)/total_cu_count > 0.5 FROM pgxc_get_small_cu_info(c.oid)) THEN 'small cu table' ELSE 'normal large table' END ELSE 'normal large table' END AS diagnostic, t1.schemaname, -- 表的schema t1.tablename, -- 表名 a.rolname AS tableowner, x.pgroup AS nodegroup, CASE x.pclocatortype WHEN 'H' THEN 'Hash' WHEN 'N' THEN 'Round Robin' WHEN 'R' THEN 'Replicate' END AS locatortype, CASE WHEN c.parttype = 'p' THEN true ELSE false END AS ispartitioned, CASE WHEN reloptions::text LIKE '%orientation=column%' THEN 'column' WHEN reloptions::text LIKE '%orientation=row%' THEN 'row' END AS orientation, t1.dnnum, -- 表的node group的DN数 t1.totalsize AS "totalsize(MB)", -- 表的size ,单位MB t1.avgsize AS "avgsize(MB)", -- 平均每个DN上数据量,单位MB t1.skewsize AS "skewsize(MB)", -- 不同DN上数据size的最大差值,单位MB t1.skewdn, -- 数据量最大的DN t1.maxratio, -- 数据量最大DN的size/平均size t1.minratio, -- 数据量最小DN的size/平均size t1.skewratio -- 不同DN上数据size的最大差值/平均size FROM ( -- 预处理,识别倾斜表 SELECT schemaname, tablename, skewdn, dnnum, totalsize, avgsize, skewsize, (maxsize/avgsize)::numeric(20,2) AS maxratio, (minsize/avgsize)::numeric(20,2) AS minratio, (skewsize/avgsize)::numeric(20,2) AS skewratio FROM ( SELECT schemaname, tablename, skewdn, count(1) AS dnnum, sum(dnsize) AS totalsize, avg(dnsize) AS avgsize, max(dnsize) AS maxsize, min(dnsize) AS minsize, (max(dnsize) - min(dnsize)) AS skewsize FROM ( --对每个表的数据按照DN数据量大小排序,以及获取倾斜的dn SELECT schemaname, tablename, nodename, (dnsize/1024/1024)::bigint AS dnsize, -- 单位换算为MB first_value(nodename) over (PARTITION BY schemaname, tablename ORDER BY dnsize DESC, nodename) AS skewdn -- --数据量最大的DN FROM ( -- 获取大于10GB的表 SELECT schemaname, tablename,(rd).nodename, ((rd).dnsize + 1) AS dnsize FROM ( SELECT schemaname, tablename, gs_table_distribution(schemaname, tablename) AS rd FROM gs_table_distribution() WHERE schemaname NOT IN ('pg_catalog', 'dbms_om', 'cstore') AND relkind = 'r' GROUP BY schemaname, tablename HAVING sum(dnsize) > 50.0 * 1024 * 1024 * 1024 -- 总大小大于100GB ) ) ) GROUP BY schemaname,tablename, skewdn ) ) t1 INNER JOIN pg_class c ON c.relname = t1.tablename LEFT JOIN pg_namespace n ON n.nspname = t1.schemaname LEFT JOIN pg_authid a ON a.oid = c.relowner LEFT JOIN pgxc_class x ON x.pcrelid = c.oid WHERE c.reloptions::text NOT LIKE '%internal_mask%' ORDER BY totalsize DESC, diagnostic, skewsize DESC ;
针对不同的诊断结果使用如下诊断措施
类别 |
建议手段 |
skew table |
根据业务选择关联常用,并且数据不倾斜的列作为修改分布列,如果找不到合适的分布列,可以把表修改为RoundRobin分布 ALTER TABLE ctmes_tgmesmbi.mbi_hlottsthisDISTRIBUTE BY ROUNDROBIN;复制
|
uncompressed column table |
列存表的压缩效果非常好,一般推荐使用压缩,至少使用low级别压缩 ALTER TABLE customer_address SET(compression=low); VACUUM FULL customer_address;复制
|
dirty table |
说明表检测碎片率比较高,需要通过VACUUM整理表 VACUUM FULL customer_address;复制
|
small cu table |
说明表小CU比较多,需要通过VACUUM整理表 VACUUM FULL customer_address;复制
|
large replicattion table |
复制表在每个DN上都有一份全量数据,当表磁盘空间占用过大时,需要把表修改为HASH表。一般复制表都是维表,存在主键,直接把分布列修改为主键 ALTER TABLE ctmes_tgmesmbi.mbi_hlottsthisDISTRIBUTE BY HASH(id);复制 |
normal large table |
3. 冗余索引诊断
目的:识别冗余索引,建议客户删除。可以降低磁盘空间,并降低大规模数据导入的时候的xlog规模
-- optimizable policy为duplicate的检查项 -- 表明两个索引字段和字段顺序完全一致 -- 建议直接删除optimizable index指定的索引; -- optimizable policy为redundancy检查项表明 -- optimizable index指定的索引的索引列刚好是base index的索引列的前面字段 -- 建议直接删除optimizable index指定的索引; -- optimizable policy为optimizable检查项 -- 表明optimizable index和base index这两个索引的索引列完全重复,但是索引列的顺序不一致 -- 这种场景需要人工介入分析是否可以优化 WITH info AS( SELECT quote_ident(n.nspname) || '.' || quote_ident(c.relname) AS tablename, pgroup AS nodegroup, x.indrelid AS indrelid, x.indexrelid AS indexrelid, indisunique, indisprimary, indnatts, indkey, indexprs FROM pg_index x INNER JOIN pg_class c ON c.oid = x.indrelid INNER JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace INNER JOIN pgxc_class xc ON xc.pcrelid = c.oid WHERE c.relkind = 'r' AND c.oid >= 16384 AND (c.reloptions IS NULL OR c.reloptions::text NOT LIKE '%internal_mask%') AND i.relkind = 'i' AND i.oid >= 16384 AND x.indpred IS NULL ), base AS( SELECT tablename, nodegroup, i.indrelid, i.indexrelid baseidx, i.indisunique AS base_unique, i.indisprimary AS base_primary, x.indexrelid AS optidx, x.indisunique AS opt_unique, x.indisprimary AS opt_primary, CASE WHEN opt_primary > base_primary OR opt_unique > base_unique THEN true ELSE false END AS swap, CASE WHEN i.indkey = x.indkey AND coalesce(pg_get_expr(i.indexprs, i.indrelid), 'NULL') = coalesce(pg_get_expr(x.indexprs, x.indrelid), 'NULL') THEN 'duplicate'::text WHEN x.indexprs IS NOT NULL OR i.indexprs IS NOT NULL THEN NULL::text WHEN strpos(i.indkey::text, x.indkey::text||' ') = 1 OR strpos(x.indkey::text, i.indkey::text||' ') = 1 THEN 'redundancy'::text WHEN i.indkey @> x.indkey AND x.indkey @> i.indkey THEN 'optimizable'::text ELSE NULL END AS optpolicy FROM info i INNER JOIN pg_index x ON (i.indrelid = x.indrelid AND i.indexrelid > x.indexrelid) WHERE x.indpred IS NULL AND optpolicy IS NOT NULL ), tmp AS( SELECT tablename, indrelid, nodegroup, CASE WHEN swap THEN optidx ELSE baseidx END AS base_idx, CASE WHEN swap THEN opt_primary ELSE base_primary END AS base_primary, CASE WHEN swap THEN opt_unique ELSE base_unique END AS base_unique, CASE WHEN swap THEN baseidx ELSE optidx END AS opt_idx, CASE WHEN swap THEN base_primary ELSE opt_primary END AS opt_primary, CASE WHEN swap THEN base_unique ELSE opt_unique END AS opt_unique, optpolicy FROM base ) SELECT tablename, nodegroup, base_idx::regclass::text AS base_index, base_primary, base_unique, substring(pg_get_indexdef(base_idx) from 'USING .+\)') AS base_idxdef, opt_idx::regclass::text AS opt_index, opt_primary, opt_unique, substring(pg_get_indexdef(opt_idx) from 'USING .+\)') AS opt_idxdef, optpolicy, pg_get_tabledef(indrelid) FROM tmp ORDER BY 1, 2, 3 ;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业
2022-03-18 详解4种微服务框架接入Istio方案
2022-03-18 想了解 spring-cloud-kubernetes,那就先来实战一把官方demo
2022-03-18 全卷积网络(FCN)实战:使用FCN实现语义分割
2022-03-18 解密GaussDB(for Influx)时序洞察
2021-03-18 化蛹成蝶,华为云DevCloud助力互联网+转型,重构钢铁产业链
2021-03-18 数仓集群管理:单节点故障RTO机制分析
2021-03-18 都在讲Redis主从复制原理,我来讲实践总结