数据库微观案例第42期

本期内容如下：

• 巧用事件触发器清理插件
• SQL优化之NOT IN
• SQL优化之行构造器
• openGauss索引列及GUC设置
• TPA自动化部署工具

实践案例：借用事件触发器清理扩展插件

https://www.crunchydata.com/blog/building-postgresql-extensions-dropping-extensions-and-cleanup

这篇文章作者巧妙利用事件触发器来清理自定义扩展插件，摘要如下：

CREATE FUNCTION bridge_stats.cleanup() RETURNS event_trigger AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
        IF obj.object_identity = 'bridge_stats' AND obj.object_type = 'extension' THEN
            PERFORM cron.unschedule('bridge-stats-weekly-maintenance');
            PERFORM cron.unschedule('bridge-stats-hourly-snapshot');
        END IF;
    END LOOP;
    DROP SCHEMA bridge_stats CASCADE;  -- the only new line in this function!
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER bridge_stats_cleanup ON sql_drop
WHEN TAG IN ('DROP EXTENSION')
EXECUTE FUNCTION bridge_stats.cleanup();

ALTER EXTENSION bridge_stats DROP EVENT TRIGGER bridge_stats_cleanup;
ALTER EXTENSION bridge_stats DROP FUNCTION bridge_stats.cleanup();
ALTER EXTENSION bridge_stats DROP SCHEMA bridge_stats;

需要将事件触发器以及清理函数与插件删除依赖绑定关系，当删除插件产生sql_drop事件时，清理函数才能正常工作。

SQL优化：案例一 NOT IN

来自于德哥的文章
5万5千倍性能提升,这种SQL优化技能你一定要掌握！
https://mp.weixin.qq.com/s/_s0hpGKSzSrSrHSUaLkIvA

摘要如下：

CREATE TABLE a(id int primary key, info text);
CREATE TABLE b(id int primary key, aid int, crt_time timestamp);
CREATE INDEX b_aid on b(aid);

建表后插入测试数据，a表插入1000条，b表插入500万条，只包含aid的500个id。

INSERT INTO a SELECT generate_series(1,1000), md5(random()::text);

INSERT INTO b SELECT generate_series(1,5000000), generate_series(1,500), clock_timestamp();

优化前执行时间大约需要618s

select * from a where id not in (select aid from b); 

优化方式一

select * from 
(
  select 
    a.* ,  
    (select aid from b where b.aid=a.id limit 1) as aid 
  from a 
) as t 
where t.aid is null;  

优化方式二

select * from a 
  where (select aid from b where b.aid=a.id limit 1) is null; 

上面两种方式将执行时间下降到18s左右，德哥还有大招：稀疏列变态优化，详情见文章。

SQL优化：案例二 行构造器

Alejandro García Montoro
Making a Postgres query 1,000 times faster
https://mattermost.com/blog/making-a-postgres-query-1000-times-faster

摘要如下：

SELECT Posts.*, Channels.TeamId
  FROM Posts LEFT JOIN Channels ON Posts.ChannelId = Channels.Id
  WHERE
    Posts.CreateAt > ?1
    OR
    (Posts.CreateAt = ?1 AND Posts.Id > ?2)
  ORDER BY Posts.CreateAt ASC, Posts.Id ASC
  LIMIT ?3;

上面的语句需要同时根据Id和和创建时间来获取N条数据，使用行构造器可以对WHERE条件进行完美改写：

CreateAt > ?1 OR (CreateAt = ?1 AND Id > ?2)

改写为：

(CreateAt, Id) > (?1, ?2)

改写前执行时间30s，改写后34ms，大约1000倍提升。

openGauss：分区表索引与GUC参数设置

• openGauss分区表全局索引与索引列问题
  分区表主键字段如果不是分区键，那创建主键约束时会包含tableoid列
  分区表非主键字段建立全局索引时也会包含tableoid列

• openGauss设置guc参数注意事项
  1.默认情况下gs_guc工具可以修改实例级的参数，并立即生效。
  2.alter database…set修改的参数只对新连接生效。
  3.alter database…set修改的优先级高于gs_guc。

工具：TPA自动化部署PostgreSQL集群

EDB开源了一款PostgreSQL集群自动化部署工具：Trusted Postgres Architect，简称TPA。

TPA底层使用Ansible编排，与Ansible之间的主要区别在于，TPA 提供了一种更简洁友好的语法来指定集群架构，详细可参考文章：<<用 TPA 自动化 PostgreSQL 集群的部署>>

与我联系

• 微信公众号：象楚之行
• 墨天轮：https://www.modb.pro/u/15675
• 微信：skypkmoon

勤耕细作，用心积微；静待花开，量变质成。