使用PostgreSQL创建统计信息

PostgreSQL统计信息：创建与优化查询性能
最新推荐文章于 2025-07-22 14:25:20 发布
最新推荐文章于 2025-07-22 14:25:20 发布
阅读量637 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： postgresql 数据库 PostgreSQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/XwlyVue/article/details/133203768
PostgreSQL 专栏收录该内容
123 篇文章 ¥59.90 ¥99.00
订阅专栏
本文详述了在PostgreSQL中创建统计信息的重要性，包括自动和手动收集方法，以帮助查询优化器提升查询效率。通过调整参数和执行特定命令，可以确保统计信息的准确性和及时性。

在PostgreSQL中，统计信息是数据库管理系统收集和存储的关于表和索引的数据特征的信息。统计信息对于查询优化器来说非常重要，它能够帮助优化器选择最佳的查询计划，提高查询性能和效率。

为了创建统计信息，我们需要使用PostgreSQL提供的自动统计信息收集功能或手动执行统计信息收集命令。下面我将详细介绍如何在PostgreSQL中创建统计信息。

  1. 自动统计信息收集

PostgreSQL默认情况下启用了自动统计信息收集功能，它会定期收集和更新表和索引的统计信息。收集统计信息的频率由配置参数autovacuum和analyze控制。

要启用自动统计信息收集，请确保autovacuum参数的值为on，这表示自动化的垃圾回收和统计信息收集功能都是启用的。可以通过以下命令检查和更改该参数：

SHOW autovacuum;
ALTER SYSTEM SET autovacuum TO on;

默认情况下，autovacuum_analyze_scale_factor参数设置为0.1，表示当表或索引的行数变化超过10%时，将自动执行统计信息收集。可以通过以下命令检查和更改该参数：

SHOW autovacuum_analyze_scale_factor;
ALTER SYSTEM SET autovacuum_analyze_scale_factor TO 0.05;
  1. 手动统计信息收集
了解本专栏 订阅专栏 解锁全文
【PostgreSQL】PostgreSQL查看收集统计信息
tttzzzqqq2018的博客
12-28 1179
【代码】【PostgreSQL】PostgreSQL查看收集统计信息。
PostgreSQL自定义统计信息
Focus on PostgreSQL
01-10 2194
pg和oracle的优化器一样是基于成本的(CBO)估算。成本估算中很重要的一个环节是估计每个执行节点返回的记录数。例如在hash join中一般都会选择记录较少的作为hash表。 pg中对于单列选择性的估算比较准确，而对于多列的情况则会出现不准确的情况，因为pg默认使用独立属性，直接以多个字段选择性相乘的方法计算多个字段条件的选择性。 pg10开始支持用户自定义统计信息，这样我们便可以针对这种多...
postgresql 的统计信息
weixin_30824479的博客
05-23 508
os: centos7.4 postgresql:9.6.8 查看os进程 $ ps -ef|grep -i post |grep -i stat postgres 10782 10770 0 May09 ? 00:02:42 postgres: stats collector process 表的信息 mo...
postgresql_internals-14 学习笔记（六）—— 统计信息
Hehuyi_In的博客
06-24 7599
不完全来自这本书，把查到的和之前的文章重新汇总整理了一把。
postgresql使用（三）：收集Postgresql数据库统计信息
⚡
02-07 9484
零、前传 postgresql使用（一）：TPC-H tools生成数据集并导入至postgre的数据库 postgresql使用（二）：在TPC-H的数据库上pgbench 压力测试 一、准备工作 官方statistics collector文档 https://www.postgresql.org/docs/9.5/monitoring-stats.html PG提供了以...
postgresql中的统计信息
小屋
04-20 3686
pg里面有一个专门的进程 statistics collector 负责对数据库，表，函数的调用次数进行统计，通过socket与执行查询的进程进行通信，当执行语句的进程，在执行一条语句时，会在执行前，把上条语句的统计信息通过socket发送给 statistics collector 进程，这样做是因为上个事务已经 commit 或 rollback 了，统计的是事务已完成的数量。   sta
在 PostgreSQL 中通过不同的系统函数和视图来获取统计信息
极客神殿
11-15 909
PostgreSQL 提供了丰富的系统函数和视图来查看和分析不同类型数据库对象的统计信息。数据库表索引视图表空间用户使用这些信息可以帮助数据库管理员进行数据库优化、监控和故障排除。
PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
xdpcxq的专栏
02-21 977
高效的查询依赖于最新的数据库统计信息，这些统计信息帮助数据库的查询优化器选择最佳的执行计划。SQL Server提供了统计信息异步自动更新功能，查询可以在统计信息过时的情况下继续执行，同时统计信息的更新会在后台自动异步运行。默认情况下，统计信息更新是同步进行的，这意味着数据库引擎在查询执行时会等待统计信息更新完成。（3）算法修改之后，统计信息自动更新阈值的算法SQRT(1,000 * n)是一条随着表的行数的变化，波动率较低的曲线，也就是意味着更倾向于在一个较小的变化之后触发统计信息自动更新。
一款非常强大的PostgreSQL系列数据库信息收集工具—pg-collector
loveshenyang的博客
07-24 2057
pg-collector 是一个强大且易于使用的 PostgreSQL 数据库监控和性能分析工具，它可以帮助数据库管理员和开发人员更好地理解和优化数据库的性能。通过定期使用 pg-collector，可以有效保障数据库的健康状态，提升应用的稳定性和响应速度。
postgresql 定时收集表和索引统计信息
weixin_33788244的博客
02-26 519
--由于pg中表和索引的信息收集都是基于时间点的，对于以往的信息无法与现在的信息进行对比，故写下此工具进行统计信息收集 --创建数据信息的schema create schema db_stat; --创建收集信息的基础表 create table db_stat.snapshot_pg_stat_all_indexes (relid int,inde...
PostgreSQL 之 统计信息
HighGO
03-27 4818
PG用于收集统计信息的进程stats collector process 源码路径 ："src/backend/postmaster/pgstat.c"统计信息的存放地启动时, 读入已存在的统计文件, 或初始化0. 数据库运行过程中存储在内存和temp文件; 启动时 : shared buffer -> "src/backend/postmaster/pgstat.c" -> ...
【PostgreSQL】PostgreSQL多元统计信息
hdhdhdk的博客
04-15 1150
例如，对于一个带有WHERE子句的查询，如果条件涉及到的列有MCV信息，优化器可以根据筛选值在MCV列表中的频率来近似计算满足条件的行数，从而更好地判断是否应该使用索引或进行全表扫描。Most Common Values (MCV)，直译为“最常见的值”，是一种针对数据库表中某一列的统计信息，用于记录该列中最常出现的值及其出现频率。MCV信息对索引选择有直接影响。当查询条件中的值属于MCV集合时，优化器更倾向于选择相关的索引来加速查询，因为这些值在数据中较为普遍，索引扫描很可能带来显著的性能提升。
postgresql信息统计分析
weixin_42767321的博客
12-21 1660
----------------------------------------------------------------------------------------------------------- -- pg_stat ---------------------------------------------------------------------------------...
PostgreSQL数据库统计信息——analyze统计信息收集
肥叔菌的博客
09-12 2434
analyze统计信息收集主要分为两个部分：一个统计信息系统表pg_statistic和pg_class的更新；二是向PgStat进程发送统计信息，其调用堆栈如下所示。
【PostgreSQL】 统计信息：查询优化与运行监控的基础支柱
最新发布
Hominid_Pro的博客
07-22 562
在 PostgreSQL 中，统计信息不仅是查询优化器制定执行计划的基础，也在数据库运行监控和自动维护中发挥着重要作用。本文将从统计信息的用途、产生方式和分类三个角度介绍统计信息。
PostgreSQL数据库统计信息——统计信息系统表
肥叔菌的博客
09-13 2484
pg_stats_ext是pg_ statistic_exts_data上的公共可读视图（在与pgUstatic_expt连接后），它仅公开当前用户可读的表和列的信息。pg_stats视图是我们经常用来观察pg_statistics系统表统计信息的工具，如下其提供了很多指标：null_frac表示null空值的比率、avg_width表示以字节为单位的平均宽度、n_distinct大于零就是非重复值的数量小于零则是非重复值的个数除以行数。pg_statistic还存储关于索引表达式的值的统计数据。
数据库系列-PostgreSQL之统计信息
Evan_L的博客
08-08 4701
这里写自定义目录标题前言统计信息统计信息有哪些表的统计信息——[pg_class](https://www.php.cn/manual/view/20908.html)单列统计信息——[pg_stats](https://www.yiibai.com/manual/postgresql/view-pg-stats.html) 前言 时至今日，已经时隔两周了。不过没关系，重点是坚持学习。说到这，最近有看到其他的书有些新的感悟：60分知识。核心思想很简单：就是要能够自主认知到某个知识对于目前的自己而言，需要达到
PostgreSQL数据库统计信息——compute_scalar_stats计算统计数据
肥叔菌的博客
09-17 1252
compare_scalars除了对项目进行ScalarItems之外，每当发现两个ScalarItems包含相等的值时，会更新tupnoLink[]数组，规则是以tupno小的值作为索引，tupnoLink对应元素里存放的是tupno大的值，tupnoLink[tupno_min]为tupno_max。在取出样本数据的时候，按照tuple在磁盘中的位置顺序取出的（ScalarItem的tupno维护了位置顺序信息），因此对值进行排序后即可计算得出相关性。条件，也就是找到的都是null值。
Postgres的索引、选择率、统计信息
liuzhilong的博客
09-30 703
Postgres的索引、选择率、统计信息
postgresql分组统计创建时间最小的id
07-02
<think>我们有一个需求：在PostgreSQL中按某个字段分组，然后获取每组中创建时间最小的记录ID。通常，我们可以使用窗口函数（如ROW_NUMBER）或DISTINCTON来实现。方法1：使用DISTINCTONDISTINCT ON是PostgreSQL特有的语法，可以按指定字段去重，并返回每个分组中的第一条记录。我们可以按分组字段排序，并指定创建时间升序，这样每组中创建时间最小的记录就会排在第一位。示例： ```sqlSELECT DISTINCT ON(group_column)id,group_column, created_timeFROM your_tableORDER BYgroup_column, created_time,id;```注意：DISTINCT ON后面的括号内指定分组字段（group_column），然后排序时先按分组字段排序，再按created_time升序（这样每组第一条就是最小时间），最后可以加一个id以保证确定性（如果同一组内有两条记录创建时间相同，则按id排序）。方法2：使用窗口函数我们可以使用ROW_NUMBER()为每个分组内的记录按创建时间排序（升序）赋予行号，然后取行号为1的记录。示例：```sqlSELECTid,group_column, created_timeFROM (SELECTid,group_column,created_time,ROW_NUMBER() OVER(PARTITION BYgroup_columnORDER BYcreated_time, id) ASrnFROM your_table) tWHERErn=1;```这里同样，如果创建时间相同，我们按id排序以确保确定性。两种方法比较： -DISTINCTON通常更简洁，且可能更高效，因为它是专门为这种场景设计的。-窗口函数更通用（在其他数据库中也适用），并且可以灵活地获取每组的前N条记录。根据需求选择合适的方法。引用说明：在PostgreSQL中，DISTINCTON是一个高效的分组取第一条记录的方法[^1]。窗口函数ROW_NUMBER()也是处理此类问题的常用手段[^2]。注意：如果表很大，请确保在分组字段和排序字段上有合适的索引，以提高查询性能。例如，对于DISTINCTON方法，可以创建索引：(group_column, created_time,id)。对于窗口函数方法，可以创建索引：(group_column,created_time, id)。相关问题：1.如何在PostgreSQL中获取每组的前N条记录？2.在PostgreSQL中，DISTINCTON和窗口函数在性能上有什么区别？3.如果分组后需要取最小和最大记录，该如何写SQL？</think>在 PostgreSQL 中获取每组中创建时间最小的记录 ID，可通过以下方法实现： ### 方法 1：使用 `DISTINCT ON`（推荐） ```sql SELECT DISTINCT ON (group_column) id, group_column, created_time FROM your_table ORDER BY group_column, created_time, id; ``` - **说明**： - `DISTINCT ON (group_column)` 按分组字段去重 - `ORDER BY group_column, created_time` 确保每组取最早时间记录 - 添加 `id` 排序保证结果确定性（防止时间相同时返回随机记录） - **性能**：通常最优，尤其对分组字段有索引时[^3] ### 方法 2：使用窗口函数 ```sql SELECT id, group_column, created_time FROM ( SELECT id, group_column, created_time, ROW_NUMBER() OVER ( PARTITION BY group_column ORDER BY created_time, id ) AS rn FROM your_table ) sub WHERE rn = 1; ``` - **说明**： - `PARTITION BY group_column` 定义分组 - `ORDER BY created_time, id` 排序取最早记录 - `rn = 1` 筛选每组第一条 ### 方法 3：使用子查询 ```sql SELECT t1.id, t1.group_column, t1.created_time FROM your_table t1 INNER JOIN ( SELECT group_column, MIN(created_time) AS min_time FROM your_table GROUP BY group_column ) t2 ON t1.group_column = t2.group_column AND t1.created_time = t2.min_time; ``` - **注意**：如果同一组存在多个相同最小时间，会返回所有匹配记录 --- ### 性能优化建议 1. **索引优化**： ```sql CREATE INDEX idx_group_created ON your_table (group_column, created_time, id); ``` 可显著加速分组排序操作[^3] 2. **处理重复值**： - 若需唯一记录，在排序中添加唯一字段（如 `id`） - 若需所有最小时间记录，使用方法 3 3. **并发创建索引**（大表适用）： ```sql CREATE INDEX CONCURRENTLY idx_group_created ON your_table (group_column, created_time); ``` 避免阻塞写入操作[^3] --- ### 示例场景 假设表 `orders` 结构： | id | customer_id | created_time | |----|-------------|--------------------| | 1 | A | 2023-01-01 10:00 | | 2 | A | 2023-01-01 09:00 | | 3 | B | 2023-01-02 08:00 | 查询每位客户的最早订单 ID： ```sql SELECT DISTINCT ON (customer_id) id, customer_id, created_time FROM orders ORDER BY customer_id, created_time, id; ``` 结果： | id | customer_id | created_time | |----|-------------|--------------------| | 2 | A | 2023-01-01 09:00 | | 3 | B | 2023-01-02 08:00 | ---
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值