阿里云
阿里云多端小程序中小企业获客首选
发表主题 回复主题
  • 21558阅读
  • 28回复

PostgreSQL 常见慢SQL问题解决方法(一)

发帖
251
云币
250

刚熟悉PG开发者接触PG,或多或少会碰到一些问题.
常见的表现是碰到一些慢SQL.这时候别急着吐槽.绝大多数并不是问题,稍微的语法调整或进行一些简单的优化就能解决问题.下面具体分析几个案例.

: 中文字符串的索引扫描慢
test =# \d testidx
                  Table"test.testidx"    
Column         |        Type                 | Modifiers
----------------+-----------------------------+-----------
id             | numeric                     |  
table_id       | numeric                     |
description    | character varying(4000)     |
user_comment   | character varying(4000)     |
encoding       | character varying(64)       |

这是一个很常见的表,它所在database Encoding Collate Ctype 都是zh_CN.UTF-8
为了检索description信息,很常见的,我们在该列上创建一个btree索引.
test=# create index  idx_testidx on testidx(description);                    
CREATE INDEX

检索列中的信息使用like语句进行匹配,发现查询计划居然没有使用索引.


test=# explain select description from testidxwhere description like 'test%';          
                         QUERY PLAN                          
-------------------------------------------------------------
SeqScan on testidx  (cost=0.00..30151.00rows=64 width=28)
  Filter: ((description)::text ~~ 'test%'::text)
(2 rows)

有点不甘心,禁掉索引看个究竟,索引是用上了,但是条件过滤放到了索引外.数据用索引扫描获取,生成了位图,然后走Bitmap Heap Scan,很明显这是有问题的.


test=# set enable_seqscan=off;
SET
test=# explain select description from testidxwhere description like 'test%';
                                     QUERYPLAN                                    
------------------------------------------------------------------------------------
Bitmap Heap Scan on testidx  (cost=29756.57..59907.57 rows=64 width=28)
  Filter: ((description)::text ~~ 'test%'::text)
  ->  Bitmap Index Scan on idx_testidx  (cost=0.00..29756.55 rows=1000000 width=0)
(3 rows)


实际的执行一遍,看看执行情况,也不乐观,1000000行数据都取了出来,堆扫描的过滤器过滤掉所有的行
执行该SQL用了接近半秒,太慢了.


test=# explain analyze select description fromtestidx where description like 'test%';
                                                               QUERY PLAN                                                              
----------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on testidx  (cost=29756.57..59907.57 rows=64 width=28)(actual time=407.548..407.548 rows=0 loops=1)
  Filter: ((description)::text ~~ 'test%'::text)
  Rows Removed by Filter: 1000000
  ->  Bitmap Index Scan onidx_testidx  (cost=0.00..29756.55rows=1000000 width=0) (actual time=166.581..166.581 rows=1000000 loops=1)
Total runtime: 407.590 ms
(5 rows)

原因很简单
1 utf8编码下表中的列需要按照utf8的规则来操作(各种操作符 > =< (~~)like )
2 创建索引时没有指定比较方式,默认采用standard"C"的字符串比较方法在UTF8上只支持 = 操作符.
3 创建索引时指定特定的比较方式能支持索引的like.
4 btree gin hash索引的用法相同.
5 pgchar varchar text数据类型适用上述规则.

The operator classes text_pattern_ops,varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the typestext, varchar, and char respectively. The difference from the default operatorclasses is that the values are compared strictly character by character ratherthan according to the locale-specific collation rules. This makes theseoperator classes suitable for use by queries involving pattern matchingexpressions (LIKE or POSIX regular expressions) when the database does not usethe standard "C" locale. As an example, you might index a varcharcolumn like this:

来看看create index的语法:

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [name ] ON table_name [ USING method ]
    ({ column_name | ( expression ) } [  ] [opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [WITH ( storage_parameter = value [, ... ] ) ]
    [TABLESPACE tablespace_name ]
    [WHERE predicate ]
collation
The name of the collation to use for theindex. By default, the index uses the collation declared for the column to beindexed or the result collation of the expression to be indexed. Indexes withnon-default collations can be useful for queries that involve expressions usingnon-default collations.

于是,可以用该语法创建对应可用的索引.
test=# create index  idx_testidx2 on testidx(description varchar_pattern_ops);
CREATE INDEX

让我们来看看效果,Index Cond出现了.使用索引扫描,并且做了条件过滤,可能有64行有效数据.
test=# explain select description from testidxwhere description like 'test%';  

                                     QUERYPLAN                                    
------------------------------------------------------------------------------------
Index Only Scan using idx_testidx2 ontestidx  (cost=0.55..8.57 rows=64width=28)
  Index Cond: ((description ~>=~ 'test'::text) AND (description ~<~'tesu'::text))
  Filter: ((description)::text ~~ 'test%'::text)
(3 rows)


实际执行结果验证了索引的有效性,SQL效率执行效果明显.


test=# explain analyze select description fromtestidx where description like 'test%';
                                                        QUERY PLAN                                                        
-----------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_testidx2 ontestidx  (cost=0.55..8.57 rows=64width=28) (actual time=0.081..0.081 rows=0 loops=1)
  Index Cond: ((description ~>=~ 'test'::text) AND (description ~<~'tesu'::text))
  Filter: ((description)::text ~~ 'test%'::text)
  Heap Fetches: 0
Total runtime: 0.105 ms
(5 rows)


发帖
4
云币
8
只看该作者 沙发  发表于: 2015-05-29
RePostgreSQL常见慢SQL问题解决方法一
我之前也碰到了这个问题,原来是这样的.学习了!
本帖最近评分记录: 1 条评分 云币 +5
belle.zhoux 云币 +5 - 2015-06-01
级别: 科学怪人
发帖
9441
云币
14683
只看该作者 板凳  发表于: 2015-06-06
   我也学习了 周大
级别: 新人
发帖
1
云币
1
只看该作者 地板  发表于: 2015-06-08
RePostgreSQL常见慢SQL问题解决方法一
太厉害了,虽然我没看懂
级别: 菜鸟
发帖
52
云币
71
只看该作者 4楼 发表于: 2015-06-09
RePostgreSQL常见慢SQL问题解决方法一
我也来学习下,厉害
级别: 新人
发帖
4
云币
19
只看该作者 5楼 发表于: 2015-08-27
RePostgreSQL常见慢SQL问题解决方法一
啊呀,原来论坛上还是有不少干货的呀。
级别: 架构狮
发帖
1438
云币
1888

只看该作者 6楼 发表于: 2015-08-31
火钳留名
级别: 小白
发帖
41
云币
2
只看该作者 7楼 发表于: 2015-09-01
RePostgreSQL常见慢SQL问题解决方法一
回帖奖励回帖奖励回帖奖励
级别: 程序猿
发帖
234
云币
275
只看该作者 8楼 发表于: 2015-10-09
RePostgreSQL常见慢SQL问题解决方法一
路过,学习了,非常感谢分享,为了答谢你们,我也有好东东想分享给大家,有隐形云账号的赶紧发过来分享去
级别: 新人
发帖
2
云币
2
只看该作者 9楼 发表于: 2015-10-19
RePostgreSQL常见慢SQL问题解决方法一
很好的干货,谢谢
级别: 新人
发帖
10
云币
20
只看该作者 10楼 发表于: 2015-10-27
RePostgreSQL常见慢SQL问题解决方法一
看高深的技术论坛,不如视频中阿里云的导师讲的透析啊
好的, 我们一起说:
学习阿里云线上课程, 尽在知途-云上征途, 全球首发,敬请登录:chinamoocs.com
级别: 程序猿
发帖
234
云币
275
只看该作者 11楼 发表于: 2015-10-29
RePostgreSQL常见慢SQL问题解决方法一
阿里云的很多产品都是很不错的,个人还是比较注重用隐形云,它是采用国密算法的,非常方便,让人放心,推荐大家也去试试看呵呵
级别: 程序猿
发帖
212
云币
259
只看该作者 12楼 发表于: 2015-11-24
RePostgreSQL 常见慢SQL问题解决方法一

如果像百度云,隐形云上面的那些数据一样,永远也不会消失就完美了
级别: 程序猿
发帖
234
云币
275
只看该作者 13楼 发表于: 2015-11-25
个人觉得这个行业和产品,在未来是有很大的发展潜力的,很看好这方面,如果可以像那个隐形云盘一样搞个加密出来就更完美了
级别: 程序猿
发帖
212
云币
259
只看该作者 14楼 发表于: 2015-11-27

如果像百度云,隐形云上面的那些数据一样,永远也不会消失就完美了
发表主题 回复主题
« 返回列表
«12»
共2页
上一主题下一主题

限100 字节
如果您提交过一次失败了,可以用”恢复数据”来恢复帖子内容
 
验证问题: 阿里云官网域名是什么? 正确答案:www.aliyun.com
上一个 下一个
      ×
      全新阿里云开发者社区, 去探索开发者的新世界吧!
      一站式的体验,更多的精彩!
      通过下面领域大门,一起探索新的技术世界吧~ (点击图标进入)