一、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_idGROUP 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_idGROUP BY fa.actor_id
复制代码
上面的两个查询功能是一样的，有人在 docker 运行 PostgreSQL 15 的环境测试对比，使用 CASE 语法会有 8%的性能损失。FILTER 的性能更好且语法更简洁。
评论