本期内容如下：

• 巧用事件触发器清理插件

• 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

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