【PostgreSQL】几个提高性能的小特性

布满荆棘的人生 2024-03-23 20:50 33阅读 0赞

### **一、LOCALE 与 “operator class”** ###

在PostgreSQL里，LOCALE默认使用C的本地化规则。LOCALE是一种文化偏好的区域设置，包括字母表、排序、数字格式等。

LOCALE里有一个比较重要的规则LC\_COLLATE，即排序方式(Collation)，它会对数据库行为有显著影响。

当数据库里运行一个like查询，DB上使用非C LOCALE，普通索引可能不支持like操作，普通索引创建时我们通常不会显式指定operator class，示例如下：

CREATE INDEX ON users (email);

那下面like查询的执行计划可以看到走全表扫描。

postgres=# EXPLAIN SELECT * FROM users WHERE email LIKE 'lukas@%';
                                     QUERY PLAN                                 
    -------------------------------------------------------------------------
     Seq Scan on users  (cost=10000000000.00..10000000001.26 rows=1 width=4463)
       Filter: ((email)::text ~~ 'lukas@%'::text)
    (2 rows)

如果进行引导，明确使用文本模式操作符text\_pattern\_ops。

CREATE INDEX ON users (email text_pattern_ops);

那下面的执行计划可以看到也能正常使用索引。

postgres=# EXPLAIN SELECT * FROM users WHERE email LIKE 'lukas@%';
                                             QUERY PLAN                                         
    --------------------------------------------------------------------------
     Index Scan using users_email_idx on users  (cost=0.14..8.16 rows=1 width=4463)
       Index Cond: (((email)::text ~>=~ 'lukas@'::text) AND ((email)::text ~<~ 'lukasA'::text))
       Filter: ((email)::text ~~ 'lukas@%'::text)
    (3 rows)

### **二、Trigger 与 Transition Table** ###

trigger触发器通常以串行方式工作，例如下面为了提高item大表count总数的性能，使用触发器更新随时将总数更新到sum表，便于快速统计。

CREATE TABLE item (
       id bigint GENERATED ALWAYS AS IDENTITY,
       value bigint NOT NULL
    );
     
    CREATE TABLE sum (
       total bigint NOT NULL
    );
     
    INSERT INTO sum VALUES (0);
     
    CREATE FUNCTION add_to_sum() RETURNS trigger
       LANGUAGE plpgsql AS
    $$BEGIN
       UPDATE sum SET total = total + NEW.value;
       RETURN NEW;
    END;$$;
     
    CREATE CONSTRAINT TRIGGER add_to_sum
       AFTER INSERT ON item
       DEFERRABLE INITIALLY DEFERRED
       FOR EACH ROW
       EXECUTE FUNCTION add_to_sum();

但是如果item表有比较频繁的批量操作，尤其还在一个事务里会引起dead tuple回收不及时，那基于触发器统计更新数据会变得越来越慢。

此时使用触发器的transition table过渡表特性，在内存中批量处理性能提升会比较明显。

DROP TRIGGER add_to_sum ON item;
     
    CREATE OR REPLACE FUNCTION add_to_sum() RETURNS trigger
       LANGUAGE plpgsql AS
    $$BEGIN
       UPDATE sum SET total = total +
          (SELECT sum(value) FROM new_rows);
       RETURN NULL;
    END;$$;
     
    CREATE TRIGGER add_to_sum
       AFTER INSERT ON item
       REFERENCING NEW TABLE AS new_rows
       FOR EACH STATEMENT
       EXECUTE FUNCTION add_to_sum();

### **三、Case 与 Filter** ###

在PostgreSQL的聚合函数中可以使用CASE或FILTER来过来数据：

##### **查询一：使用case** #####

SELECT
      fa.actor_id,
      SUM(CASE WHEN rating = 'R' THEN length END),
      SUM(CASE WHEN rating = 'PG' THEN length END)
    FROM film_actor AS fa 
    LEFT JOIN film AS f 
      ON f.film_id = fa.film_id
    GROUP BY fa.actor_id

##### **查询二：使用filter** #####

SELECT
      fa.actor_id,
      SUM(length) FILTER (WHERE rating = 'R'),
      SUM(length) FILTER (WHERE rating = 'PG'),
    FROM film_actor AS fa 
    LEFT JOIN film AS f 
      ON f.film_id = fa.film_id
    GROUP BY fa.actor_id

上面的两个查询功能是一样的，有人在docker运行PostgreSQL 15的环境测试对比，使用CASE语法会有8%的性能损失。FILTER的性能更好且语法更简洁。

### **四、开发界的强者** ###

JNPF，很多人都尝试用过它，它是功能的集大成者，任何信息化系统都可以基于它开发出来。

低代码是将开发过程中某些重复出现的场景、流程，具象化成一个个组件、api、数据库接口，避免了重复造轮子。因而极大的提高了程序员的生产效率。

官网：[www.jnpfsoft.com/?csdn][www.jnpfsoft.com_csdn] ，如果你有闲暇时间，可以做个知识拓展。

采用业内领先的SpringBoot微服务架构、支持SpringCloud模式，完善的平台扩增基础，满足了系统快速开发、灵活拓展、无缝集成和高性能应用等综合能力；采用前后端分离模式，前端和后端的开发人员可分工合作负责不同板块。

为了支撑更高技术要求的应用开发，从数据库建模、Web API构建到页面设计，与传统软件开发几乎没有差异，只是通过低代码可视化模式，减少了构建“增删改查”功能的重复劳动

[www.jnpfsoft.com_csdn]: http://www.jnpfsoft.com/?csdn