Postgresql查询效率计算初探_PostgreSQL

来源:脚本之家  责任编辑:小易  

postgresql(8.2)的配置文件中有一个参数log_min_duration_statement,意思是只log执行时间大于设定值的语句,如果设为0,表示log所有语句;如果设为-1,表示不log任何语句。看起来,这个配置选项对性能的调整是很有用的,比如可以设置:log_min_duration_statement=1000则只log执行时间大于1s的语句,重点优化这些sql语句就好了。然而,奇怪的,这个选项不太容易生效!经过反复试验,原来需要如下配置:debug_print_parse=offdebug_print_rewritten=offdebug_print_plan=offdebug_pretty_print=offlog_connections=offlog_disconnections=offlog_duration=offlog_line_prefix='%t[%p]:[%l-1]'#Special values:u=user named=database namer=remote host and porth=remote hostp=PIDt=timestamp(no milliseconds)m=timestamp with millisecondsi=command tagc=session idl=session line numbers=session start timestampx=transaction idq=stop here in non-sessionprocesses'%'e.g.'<%u%%%d>'log_statement='none'#none,mod,ddl,alllog_statement='all'#none,mod,ddl,alllog_hostname=off注意看上面的其中两个选项的设置:log_duration=offlog_statement='none'这两个选项的意思是不log任何sql语句和执行时间,但是恰恰是关闭了这两个,log_min_duration_statement才会生效!可能postgresql内部 对这两个选项做了“互斥”处理吧www.zgxue.com防采集请勿采集本网。

摘要

关系数据库很重要的一个方面是查询速度。查询速度的好坏,直接影响一个系统的好坏。

SERIAL类型的字段和MySQL中的自增唯一ID等价。当你在你的数据表中定义了一个SERIAL类型的列后,SERIAL的自增功能会被自动添加到数据库

查询速度一般需要通过查询规划来窥视执行的过程。

解决办法:首先尽量避免模糊查询,如果因为业务需要一定要使用模糊查询,则至少保证不要使用全模糊查询,对于右模糊查询,即like‘…%’,是会使用索引的;左模糊like‘%.’无法直接使用索引,但

查询路径会选择查询代价最低的路径执行。而这个代价是怎么算出来的呢。

将里面的python和interfrated terminal/console的配置看看,主要差别就是cwd那后面的路径。放上去解释说就是个运行路径,默认是null,要设为和python一样的workspace这才正常了!

主要关注的参数和表

order by后加个limit1 select.from.order by.limit 1

参数:来自postgresql.conf文件,可以通过show 来查看

也支持 like '%xx%',full text 是内建在SQL 引擎中的,不需要另外开启,速度可以提升30x左右。

seq_page_cost = 1.0 # measured on an arbitrary scalerandom_page_cost = 4.0 # same scale as abovecpu_tuple_cost = 0.01 # same scale as abovecpu_index_tuple_cost = 0.005 # same scale as abovecpu_operator_cost = 0.0025 # same scale as aboveparallel_tuple_cost = 0.1 # same scale as aboveparallel_setup_cost = 1000.0 # same scale as above

表(视图): pg_class(主要关注relpages, reltuples), pg_stats

分析简单的查询的成本计算过程

建立模拟数据,插入100000条数据进入一个表

create table test(id int, info text);insert into test(id, info) select i, md5(i::text) from generate_series(1, 100000) t(i);

没有索引的情况

分析全表查询的成本计算过程

postgres=# analyze test; #防止没有分析postgres=# explain select * from test; QUERY PLAN ------------------------------------------------------------- Seq Scan on test (cost=0.00..1834.00 rows=100000 width=37)

1.查询pg_class表,查看test表的page数量和行数

postgres=# select t.relpages, t.reltuples from pg_class t where t.relname = 'test'; relpages | reltuples ----------+----------- 834 | 100000

成本为1834.00是怎么算出来的?

2.这个过程,实际上是顺序扫描了834个page,节点发射了100000行

3.查看配置参数

seq_page_cost = 1.0 cpu_tuple_cost = 0.01

4.得出的结果就是

postgres=# select 834 * 1.0 + 100000 * 0.01; ?column? ---------- 1834.00

5.得出来的查询成本就是 1834.00。和上面的查询计划算出来的一致。

全表加入条件的成本计算过程

postgres=# explain select * from test where id = 100; QUERY PLAN -------------------------------------------------------- Seq Scan on test (cost=0.00..2084.00 rows=1 width=37) Filter: (id = 100)

成本 2084.00是怎么算出来的?

1.查询pg_class表, pages,tuples和上面的例子一样

2.这个过程就是顺序test表,发射100000行,然后通过云存过滤了100000行

3.查看过滤运算一行的代价

cpu_operator_cost = 0.0025

4.得出的结果是

postgres=# select 834 * 1.0 + 100000 * 0.01 + 100000 * 0.0025; ?column? ----------- 2084.0000

加入索引的情况

```create index on test(id);```

对比下面的四种情况

Index Only Scan

postgres=# explain select id from test where id = 100; QUERY PLAN ----------------------------------------------------------------------------- Index Only Scan using test_id_idx on test (cost=0.29..8.31 rows=1 width=4) Index Cond: (id = 100)

Index Scan

postgres=# explain select * from test where id = 100; QUERY PLAN ------------------------------------------------------------------------- Index Scan using test_id_idx on test (cost=0.29..8.31 rows=1 width=37) Index Cond: (id = 100)

Index Scan

postgres=# explain select * from test where id < 100; QUERY PLAN ---------------------------------------------------------------------------- Index Scan using test_id_idx on test (cost=0.29..10.11 rows=104 width=37) Index Cond: (id < 100)

把数据乱序插入

truncate table test;insert into test(id, info) select i, md5(i::text) from generate_series(1, 1000000) t(i) order by random();

postgres=# explain select * from test where id < 100; QUERY PLAN ---------------------------------------------------------------------------- Bitmap Heap Scan on test (cost=5.22..380.64 rows=102 width=37) Recheck Cond: (id < 100) -> Bitmap Index Scan on test_id_idx (cost=0.00..5.19 rows=102 width=0) Index Cond: (id < 100)

结论

有索引的时候,成本会大大减少。 执行计划跟数据的分布有很大的关系。 有索引的分析相对复杂一点,可以先参考官方源码实现。后面再补充上来

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对真格学网的支持。

一、使用EXPLAIN:PostgreSQL为每个查询都生成一个查询规划,因为选择正确的查询路径对性能的影响是极为关键的。PostgreSQL本身已经包含了一个规划器用于寻找最优规划,我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划。PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树,其中最底层的节点为表扫描节点,用于从数据表中返回检索出的数据行。然而,不同的扫描节点类型代表着不同的表访问模式,如:顺序扫描、索引扫描,以及位图索引扫描等。如果查询仍然需要连接、聚集、排序,或者是对原始行的其它操作,那么就会在扫描节点"之上"有其它额外的节点。并且这些操作通常都有多种方法,因此在这些位置也有可能出现不同的节点类型。EXPLAIN将为规划树中的每个节点都输出一行信息,显示基本的节点类型和规划器为执行这个规划节点计算出的预计开销值。第一行(最上层的节点)是对该规划的总执行开销的预计,这个数值就是规划器试图最小化的数值。这里有一个简单的例子,如下:复制代码 代码如下:EXPLAIN SELECT*FROM tenk1;QUERY PLANSeq Scan on tenk1(cost=0.00.458.00 rows=10000 width=244)EXPLAIN引用的数据是:1).预计的启动开销(在输出扫描开始之前消耗的时间,比如在一个排序节点里做排续的时间)。2).预计的总开销。3).预计的该规划节点输出的行数。4).预计的该规划节点的行平均宽度(单位:字节)。这里开销(cost)的计算单位是磁盘页面的存取数量,如1.0将表示一次顺序的磁盘页面读取。其中上层节点的开销将包括其所有子节点的开销。这里的输出行数(rows)并不是规划节点处理/扫描的行数,通常会更少一些。一般而言,顶层的行预计数量会更接近于查询实际返回的行数。现在我们执行下面基于系统表的查询:复制代码 代码如下:SELECT relpages,reltuples FROM pg_class WHERE relname='tenk1';从查询结果中可以看出tenk1表占有358个磁盘页面和10000条记录,然而为了计算cost的值,我们仍然需要知道另外一个系统参数值。复制代码 代码如下:postgres=show cpu_tuple_cost;cpu_tuple_cost0.01(1 row)cost=358(磁盘页面数)+10000(行数)*0.01(cpu_tuple_cost系统参数值)下面我们再来看一个带有WHERE条件的查询规划。复制代码 代码如下:EXPLAIN SELECT*FROM tenk1 WHERE unique1;QUERY PLANSeq Scan on tenk1(cost=0.00.483.00 rows=7033 width=244)Filter:(unique1)EXPLAIN的输出显示,WHERE子句被当作一个"filter"应用,这表示该规划节点将扫描表中的每一行数据,之后再判定它们是否符合过滤的条件,最后仅输出通过过滤条件的行数。这里由于WHERE子句的存在,预计的输出行数减少了。即便如此,扫描仍将访问所有10000行数据,因此开销并没有真正降低,实际上它还增加了一些因数据过滤而产生的额外CPU开销。上面的数据只是一个预计数字,即使是在每次执行ANALYZE命令之后也会随之改变,因为ANALYZE生成的统计数据是通过从该表中随机抽取的样本计算的。如果我们将上面查询的条件设置的更为严格一些的话,将会得到不同的查询规划,如:复制代码 代码如下:EXPLAIN SELECT*FROM tenk1 WHERE unique1;QUERY PLAN内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • postgresql中使用dblink实现跨库查询的方法
  • 在postgresql中实现递归查询的教程
  • postgresql树形结构的递归查询示例
  • pgsql查询优化之模糊查询实例详解
  • sql server数据迁移至postgresql出错的解释以及解决方案
  • postgresql中使用dblink实现跨库查询的方法
  • linux下创建postgresql数据库的方法步骤
  • postgresql图(graph)的递归查询实例
  • postgresql教程(四):数据类型详解
  • 在windows下手动初始化postgresql数据库教程
  • postgresql数据库中跨库访问解决方案
  • postgresql教程(十五):系统表详解
  • postgresql树形结构的递归查询示例
  • postgresql数据库事务实现方法分析
  • 如何测试很多个用户同时查询postgresql数据库时的效率
  • 如何查看PostgreSQL执行效率低的SQL
  • PostgreSQL 查询效率
  • 如何查看PostgreSQL执行效率低的SQL
  • 如何查看PostgreSQL执行效率低的SQL
  • Postgresql慢查询原因查找
  • 如何快速用python查询postgresql数据库的行数
  • postgresql 怎么查询第一条数据
  • postgresql 模糊查询命令???
  • postgresql 模糊查询
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页postgresqlpostgresql中使用dblink实现跨库查询的方法在postgresql中实现递归查询的教程postgresql树形结构的递归查询示例pgsql查询优化之模糊查询实例详解sql server数据迁移至postgresql出错的解释以及解决方案postgresql中使用dblink实现跨库查询的方法linux下创建postgresql数据库的方法步骤postgresql图(graph)的递归查询实例postgresql教程(四):数据类型详解在windows下手动初始化postgresql数据库教程postgresql数据库中跨库访问解决方案postgresql教程(十五):系统表详解postgresql树形结构的递归查询示例postgresql数据库事务实现方法分析postgresql 角色与用户管理介绍windows下postgresql数据库的下载windows下postgresql安装图解15个postgresql数据库实用命令分postgresql中的oid和xid 说明windows postgresql 安装图文教程postgresql alter语句常用操作小postgresql 安装和简单使用postgresql 创建表分区postgresql新手入门教程postgresql 数据库性能提升的几个方面postgresql备份和增量恢复方案postgresql中调用存储过程并返回数据集实postgresql管理工具phppgadmin入门指南windows上postgresql安装配置教程sqlite教程(七):数据类型详解postgresql教程(十一):服务器配置phppgadmin 配置文件参数说明中文版postgresql树形结构的递归查询示例postgresql 查看数据库,索引,表,表空间
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved