CREATE INDEX "idx_info_201903_1" ON "public"."info_201903" USING btree (
"id" "pg_catalog"."int4_ops" ASC NULLS LAST
CREATE INDEX "idx_info_201903_2" ON "public"."info_201903" USING btree (
"identity_no" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
测试的SQL很简单，结合业务采用分页的方式，先测总的记录条数，再取每页的结果集。测试精确查询、带前后缀的模糊查询：
SELECT count(*) FROM info WHERE identity_no = '沪200000C544' ;
SELECT * FROM info WHERE identity_no= '沪200000C544' LIMIT 30 OFFSET 50000;
SELECT count(*) FROM info WHERE identity_no like '京102%' ;
SELECT * FROM info WHERE identity_no like '京102%' LIMIT 30 OFFSET 50000;
SELECT count(*) FROM info WHERE identity_nolike '%S888' ;
SELECT * FROM info WHERE identity_no like '%S888' LIMIT 30 OFFSET 50000;
整个测试环境下，单表分12个区，共计2亿条数据。实际测试中，该字段进行精确查询时，平均耗时在10-200ms这个范围内；但模糊查询时，特别是无前缀的情况下，耗时均超过5分钟，导致业务严重不可用。即使是带前缀的查询，通过执行计划(explain)查看，虽然使用了索引，但由于数据量实在过于庞大，查询依旧很慢。因为分区也做了、索引也用了、SQL也已经简单到不能继续优化的地步，考虑了如下的调优思路。
●使用物理服务器
测试过程中使用的是虚拟机作为服务器，性能势必没有直接使用物理服务器高，实际生产过程中建议使用物理服务器作为数据库服务器。
●增加内存大小
通过Linux命令top查看，目前PostgreSQL已经申请了64GB内存的近62GB作为缓存使用，而且在执行计划中可以看到有不少查询会进行Bitmap Heap Scan节点操作，该节点会将索引查出的数据一次取出放入内存进行排序，如果取出的数据过多，内存放不上，势必会分步操作，将部分数据先存放磁盘，再加载进内存，如此往复，势必没有直接一次放入内存的效果好。
●使用NVMe协议的固态硬盘
通过Linux命令iostat查看，最大的瓶颈来源于磁盘I/O，已经处于满负荷运转了。虚拟机使用的是固态硬盘，该硬盘使用AHCI协议，SATA3.0接口，理论顺序读取速度仅500MB/s，实际使用中因其他进程等影响，读取速度会更慢。若更换为NVMe协议的固态硬盘，PCI-E或U.2的接口，理论带宽32Gbps，能极大提高磁盘I/O速率。
●将数据库安装在内存
PostgreSQL不能直接装在内存，只能将高频数据加载进内存。若想把程序和所有数据全部安装在内存可以考虑使用曲线救国的方案。即将内存虚拟为硬盘，把内存当硬盘使用，但由于内存的读写效率是远高于机械硬盘，甚至固态硬盘的，因此这种情况下I/O效率会非常高。对于Linux系统，可以采用挂载分区的方式，将内存等同于硬盘逻辑分区使用；对于Windows系统，可以采用Primo Ramdisk软件将内存虚拟为硬盘。这种方案风险很高，即使使用UPS进行不间断供电，解决断电时数据擦除问题，也无法保证内存硬件故障时数据丢失；同时，这种方案的经济成本也会很高。
●业务调整
此条方案适合特定的查询。例如在2亿量级数据查询时，用户即使只选择某几天时间段的数据，因为数量实在太多(数十万甚至上百万条)，按原本分页需求，需要先查询总记录条数，再查询所选页的记录数。通过执行计划可以看到，耗时慢查询主要是前者，后者返回相对较快。即使总记录条数可以使用异步的方式返回给前端页面，用户依旧需要等两三分钟才能看到共多少条记录以及共多少页。与其这样，不如设定一个阈值，例如5秒内未查询出记录条数，直接异步通知前端页面条数10000+，同时不再去执行count(*)的操作，减少系统I/O开销。这种非精确的计数业务上并不会造成不好的用户体验，即使百度采用搜索引擎来做查询，返回的总数也是用“约”进行修饰的。
用户可以正常翻页，如果其输入的页码过大，导致查询不到数据，页面只需做一个友好跳转进行提示即可。因此没必要在此业务环境去浪费过多的I/O资源。需要查询某时间段内数据总数的统计分析业务环境再单独去进行完整的count(*)操作，或者使用一些预加载的技术。
●修改默认配置项
修改postgresql.conf中的默认配置项，根据网上的一些案例实际测试修改过random_page_cost = 1；fsync = off；parallel_tuple_cost = 0.1；max_parallel_workers_per_gather = 4。通过观察发现实际收效很低，几乎无明显变化，慢查询过程中依旧是在进行高负荷的磁盘读写。通过PostgreSQL的配置项针对性调整，理论上是能提高数据库性能的，但该方案还需继续深入研究与测试。
●启用Gin (Generalized Inverted Index)索引（仅适用于非前缀模糊查询）
在不更换数据库的前提下，借鉴搜索引擎中倒排索引的思路，对查询限制条件对应字段建立Gin索引。目前数据库建立索引的常规做法是采用Btree形式，由于其二叉树的特性，排序和查询会非常快。而通过测试我们发现，现实业务中，可能会存在identity_no的模糊查询，特别是无前缀的条件下，Btree索引会失效，因此考虑使用Gin索引。根据官方手册，Gin索引是用来加快全文搜索的，适合做模糊查询和正则查询。
为了验证Gin索引是否适用，进行1000W量级的数据测试：
| 数据库 | PostgreSQL 10.4 |
| 操作系统 | CentOS Linux release 7.2.1511 |
| SSH连接客户端 | PuTTY 0.7 |
| 测试表单 | info_gin |
| 分区情况 | 分区名 | 数据量 |
| 201802 | 627001 |
| 201803 | 862729 |
| 201804 | 833407 |
| 201805 | 860097 |
| 201806 | 834221 |
| 201807 | 859458 |
| 201808 | 863205 |
| 201809 | 833041 |
| 201810 | 859668 |
| 201811 | 833983 |
| 201812 | 857917 |
| 201901 | 863472 |
| 201902 | 12414 |
| 总计 | 10000613 |
| 分区索引情况 | 无索引(字段格式varchar) | 占10GB空间 |
| 对identity_no建立Btree索引(字段格式varchar) | 占10GB空间 |
| 对identity_no建立Gin索引(字段格式text) | 占10GB空间 |
| 对identity_no建立Gist索引(字段格式text) | 占10GB空间 |
接下来分别测试Btree索引、Gin索引两种情况下，进行模糊查询的耗时，结果如下表：
| | Btree索引 | Gin索引 |
| count(*) like '京%'' | 13.49秒 | 16.34秒 |
| count(*) like '京10%' | 5.71秒 | 10.14秒 |
| count(*) like '京1042%'' | 0.78秒 | 4.05秒 |
| Limit 30 like '京%'' | 0.25秒 | 7.33秒 |
| Limit 30 like '京10%'' | 1.31秒 | 6.34秒 |
| Limit 30 like '京1042%'' | 0.50秒 | 2.54秒 |
| count(*) like '%55' | 3.79秒 | 1.31秒 |
| Limit 30 like '%55" | 2.62秒 | 1.01秒 |
| count(*) like '%00D5%' | 3.99秒 | 1.81秒 |
| Limit 30 like '%00D5%' | 2.07秒 | 1.02秒 |
值得注意的是，Gin索引天生是用于全文检索的场景，因此，需要将identity_no字段从varchar类型更换为tsvector类型。PostgreSQL本身不支持中文分词，因此还需要安装额外的开源插件scws和zhparser。实际测试发现，对于identity_no字段分词效果不明显，因为仅有一个中文，其后也不存在完整英文单词，所以利用倒排索引分词匹配的效果不好。结合执行计划可以看到带上前缀进行模糊查询时，走了全表顺序扫描，效率反而不如加Btree索引，仅有后缀和中缀查询效果略好一些。同时，考虑到使用Gin索引的查询SQL语句也和常规写法不同，例如正常写select count(*) from info_gin where identity_no like '京%'使用Gin索引时需要写为select count(*) from info_gin where identity_no @@ '京:*'，这种SQL的变化，对于ORM框架是否友好需要进一步求证。总之，因为identity_no字段中内容很短，拆分分词进行倒排索引检索效果一般，该方案仅作为一种思路参考。
●启用Gist(Generalized Search Tree)索引（仅适用于非前缀模糊查询）
Gist索引是一种基于广义搜索树建立的索引，网络上有相关案例用其做中缀模糊查询。为了验证Gist索引实际使用效果，进行1000W量级数据测试，测试条件如上一条所示。从占用空间来说，Gist与其他索引无异。接下来分别测试Btree索引、Gist索引两种情况下，进行模糊查询的耗时，结果如下表：
| | Btree索引 | Gist索引 |
| count(*) like '京%'' | 13.49秒 | 19.45秒 |
| count(*) like '京10%' | 5.71秒 | 12.25秒 |
| count(*) like '京1042%'' | 0.78秒 | 3.25秒 |
| Limit 30 like '京%'' | 0.25秒 | 6.91秒 |
| Limit 30 like '京10%'' | 1.31秒 | 6.22秒 |
| Limit 30 like '京1042%'' | 0.50秒 | 1.74秒 |
| count(*) like '%55' | 3.79秒 | 1.80秒 |
| Limit 30 like '%55" | 2.62秒 | 0.87秒 |
| count(*) like '%00D5%' | 3.99秒 | 1.25秒 |
| Limit 30 like '%00D5%' | 2.07秒 | 0.32秒 |
测试结果与Gin索引类似，对于带前缀的模糊查询，效果均不如Btree索引，仅带后缀和中缀的效果要优于Btree索引。该方法同样具有劣势，根据官方手册介绍，Gist索引查出的数据是“有损(lossy)”的，即可能有出现丢失，不适合精确使用的业务场景。不过对比Gin索引，建立Gist索引在SQL的语法上依旧是使用like和%进行模糊查询，兼容性较好。
笔者并非专业DBA，只是临时帮忙测试下PostgreSQL的性能，因发现性能瓶颈，自己学习思考了一些调优思路，不一定正确，大家辩证看待，如果有其他方案，也希望能留言一起交流分享。由于涉及具体公司业务，详细的表单结构、索引设置、数据库参数等不能和大家分享，因此还需要大家针对具体问题具体分析，从这三个方面也考虑下是否可以调优。
1、Create Index Directly
2、Change Conditions to Use Index
3、尽量避免在where子句中对字段进行运算，导致查询规划器放弃使用index
4、尽量避免在where子句中对字段类型进行强制转换，导致查询规划器放弃使用index
5、少用outer join，减少不必要的sub-query层级数【在不影响得到正确结果的前提下】
6、坚决避免select * 和 redundant columns【多余字段】
7、Index on Expressions
8、Partial Indexes
9、Decompose DDL【分解DDL】
10、Comprehensive optimization【综合优化】
11、索引的创建
12、查找需要删除的索引
13、查找重复的索引
14、查找需要维护的索引，并自定创建索引维护SQL
15、一个index size影响query plan的例子
本文将对一个任意范围按ID分组查出每个ID对应的最新记录的CASE做一个极致的优化体验。 优化后性能维持在可控范围内，任意数据量，毫秒级返回，性能平稳可控。 比优化前性能提升1万倍。
有一张数据表，结构：
CREATE TABLE target_position (
target_id varchar(80),
time big...
阅读使人充实，讨论使人敏捷，写作使人精确。
需求场景假设
Aken某天在咖啡店听到一首歌，觉得很好听，但不知道具体的歌名，只知道歌曲是“民谣”，歌词包含“一把破吉他”、“旅人”，然后是男歌手。
如果我想收藏该歌曲，请问小编如何为我找到目标歌曲、对应的歌手、专辑？
为什么要讨论这个问题
首先，搜索需求的实际场景实在太多了，可以说处处可见，比如：
百度、Google输入关键字搜索信息；
泛娱乐行业搜索对应的目标音视频文件；
人脸识别、指纹验证、特定动作捕捉认证等。
其次，物联网时代网络产生的数据信息浩如烟海
前模糊(有前缀的模糊)，后模糊(有后缀的模糊)，前后模糊(无前后缀的模糊)，正则匹配都属于文本搜索领域常见的需求。
PostgreSQL拥有很强的文本搜索能力，除了支持全文检索，还支持模糊查询、正则查询。内置的pg_tr...
故事从好多年前说起。想必大家也听说过数据库单表建议最大2kw条数据这个说法。如果超过了，性能就会下降得比较厉害。巧了。我也听说过。但我不接受它的建议，硬是单表装了1亿条数据。这时候，我们组里新来的实习生看到了之后，天真无邪地问我："单表不是建议最大两千万吗？为什么这个表都放了1个亿还不分库分表"？我能说我是因为懒吗？我当初设计时哪里想到这表竟然能涨这么快。。。我不能。说了等于承认自己是开发组里的毒瘤，虽然我确实是，但我不能承认。我如坐针毡，如芒刺背，如鲠在喉。开始了一波骚操作。"我这么做是有道理的""虽然这
正则匹配和模糊匹配通常是搜索引擎的特长，但是如果你使用的是 PostgreSQL 数据库照样能实现，并且性能不赖，加上分布式方案 (譬如 plproxy, pg_shard, fdw shard, pg-xc, pg-xl, greenplum)，处理百亿以上数据量的正则匹配和模糊匹配效果杠杠的，同时还不失数据库固有的功能，一举多得。
物联网中有大量的数...
为什么postgresql最大的单表只能是32TB？这需要从数据文件来说起。在postgresql中，一张表对应多个数据文件。数据文件中存储的是page，每一个page都有一个单独的编号，因为pg寻址空间采用的是32位，也就是2^32=4294967296，也就是一组数据文件中最多存放这些page。按照默认的block_size设置为8K，可以计算出来一组数据文件最大的大小是32T。这里，注意我说...
当数据表数据量较大时，pg数据库的存取性能会有较大的下降，因此官方建议当一个数据表的大小接近物理内存大小的时候，就应该采取分表操作。
pg数据库官方支持两种分表方式，都是讲逻辑上的一个数据表，在磁盘上分区存储，一种是pg数据库自己维护的声明式分区，另一种自行维护的继承式分区。
声明式分区不需要自行维护分区界限，不需要自己编写插入逻辑，支持范围分区、列表分区、哈希分区，支持子分区，支持将现有数据表挂接为一个分区表的分区，
创建分区表
CREATE TABLE measurement (
