一、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 ,如果你有闲暇时间,可以做个知识拓展。
采用业内领先的SpringBoot微服务架构、支持SpringCloud模式,完善的平台扩增基础,满足了系统快速开发、灵活拓展、无缝集成和高性能应用等综合能力;采用前后端分离模式,前端和后端的开发人员可分工合作负责不同板块。文章来源:https://www.toymoban.com/news/detail-645912.html
为了支撑更高技术要求的应用开发,从数据库建模、Web API构建到页面设计,与传统软件开发几乎没有差异,只是通过低代码可视化模式,减少了构建“增删改查”功能的重复劳动文章来源地址https://www.toymoban.com/news/detail-645912.html
到了这里,关于【PostgreSQL】几个提高性能的小特性的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!