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

这篇具有很好参考价值的文章主要介绍了【PostgreSQL】几个提高性能的小特性。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、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模式,完善的平台扩增基础,满足了系统快速开发、灵活拓展、无缝集成和高性能应用等综合能力;采用前后端分离模式,前端和后端的开发人员可分工合作负责不同板块。

为了支撑更高技术要求的应用开发,从数据库建模、Web API构建到页面设计,与传统软件开发几乎没有差异,只是通过低代码可视化模式,减少了构建“增删改查”功能的重复劳动文章来源地址https://www.toymoban.com/news/detail-645912.html

到了这里,关于【PostgreSQL】几个提高性能的小特性的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • PostgreSQL 16新特性之数组抽样和随机排序

    PostgreSQL 支持内置的数组类型,包括多维数组。不仅如此,PostgreSQL 还提供了许多操作和处理数组类型的函数。 PostgreSQL 16 又增加了 2 个新的数组函数: array_sample(),返回随机抽取的 N 个数组元素; array_shuffle(),将数组中的元素随机排序。 接下来我们看几个示例。 以下示例每

    2024年02月09日
    浏览(34)
  • 功能强大的PostgreSQL没有MySQL流行的10个原因,你知道几个?

    以下内容是根据玖章算术CEO叶正盛在「3306π 数据库朋友圈」技术演讲整理。文末附完整的PPT下载链接! MySQL与PostgreSQL都是非常优秀并且非常成功的数据库,PostgreSQL的功能比MySQL强大,但是依然没有MySQL流行,作者从产品功能、技术架构、生态、品牌商业几方面来分析原因。

    2024年02月07日
    浏览(45)
  • POSTGRESQL 压力测试结果与 POSTGRESQL CPU OR 内存 提升性能提升大

    数据库与硬件之间的关系,是一个决定数据库性能,必要条件,即使你参数调整的漂亮,你的SQL 撰写的没有问题,但是硬件不行,那么上面说的这一切对于数据库的性能,只能是杯水车薪。 那么如何对一个数据库或者一个应用要使用的数据库,预先通过压测的方式来满足应

    2024年02月12日
    浏览(49)
  • 在PostgreSQL中如何实现分区表以提高查询效率和管理大型表?

    随着数据量的增长,单一的大型表可能会遇到性能瓶颈和管理难题。PostgreSQL的分区表功能允许我们将一个大型表分割成多个较小的、更易于管理的片段,称为分区。这不仅可以提高查询效率,还可以简化数据管理和维护。 1. 确定分区键 首先,我们需要确定一个或多个列作为

    2024年04月25日
    浏览(47)
  • postgresql 性能调优

    性能调优是为了提高 PostgreSQL 数据库的性能和响应速度。下面是一些常见的 PostgreSQL 性能调优技巧: 定期进行数据库维护:对数据库进行定期的备份、清理和优化操作,包括 VACUUM、ANALYZE、REINDEX 等,以保持数据库的健康状态, 定期进行数据库维护是保持 PostgreSQL 数据库健康状

    2024年02月11日
    浏览(44)
  • PostgreSQL (七) 性能优化

    序号 顺序 1 FROM 2 ON 3 JOIN 4 WHERE 5 GROUP BY 6 HAVING 7 DISTINCT 8 ORDER BY 9 LIMIT 序号 优化 1 尽量不使用子查询 2 索引的数量不宜过多, 每次新增或修改数据都会新建或修改索引, 消耗性能, 因此不要超过5个 3 尽量不要使用*查询所有字段, 否则索引失效 4 使用排序时, 尽量使用有索引字段

    2024年02月09日
    浏览(54)
  • PostgreSQL 性能优化: 等待事件

    等待事件是 PostgreSQL 的重要优化工具。当您能查明会话为什么在等待资源以及会话在做什么时,您就能更好地减少瓶颈。您可以使用本节中的信息来查找可能的原因和纠正措施。 目录 等待事件概述 BufferPin Client - ClientRead Client - ClientWrite CPU Extension IO - BufFileRead IO - BufFileWrit

    2024年02月03日
    浏览(32)
  • PostgreSQL 17新特性:PL/pgSQL支持数组%TYPE以及%ROWTYPE

    正在开发中的 PostgreSQL 17 即将增加一个新功能:PL/pgSQL 支持定义伪类型 %TYPE以及%ROWTYPE 数组。 当我们使用 PL/pgSQL 编写存储过程或者函数时,可以定义不同类型的变量。例如: 在此基础上,我们可以通过 %TYPE 以及 %ROWTYPE 定义伪类型,例如: 变量 user_id 的类型基于字段 users

    2024年01月25日
    浏览(46)
  • 【PostgreSQL-16新特性之普通用户的保留连接个数(reserved_connections)】

    #【奖牌赠送开启】送你一枚荣誉奖牌,感谢你的辛勤付出!# PostgreSQL数据库为了保证在高并发,高连接数情况下某些用户能够正常连接到数据库里,设立了几个用户连接的保留个数。 本文介绍了PostgreSQL16版本前为超级用户保留的连接数(superuser_reserved_connections)以及Postgre

    2024年02月12日
    浏览(34)
  • PostgreSQL性能调优:优化查询和索引设计

    随着数据量的增长和业务需求的变化,数据库性能成为了许多企业关注的焦点之一。在众多的数据库管理系统中,PostgreSQL因其稳定性和可靠性而备受青睐。然而,即使是最强大的系统也需要合适的调优,以确保其能够高效地处理大规模数据和复杂查询。 本文将介绍如何在P

    2024年02月07日
    浏览(53)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包