postgresql - Postgres:为什么选择count(*)需要这么长时间

Question

TL；博士
我有一个问题表，查询速度很慢。我在上面运行pg_repack来重建表，但仍然很慢。不幸的是，pg_repack没有重建表。我不得不通过pg_dump转储并重新加载表。
分析显示有很多死行。

# analyse verbose payslip;
INFO:  analyzing "public.payslip"
INFO:  "payslip": scanned 30000 of 458337 pages, containing 8732 live rows and 400621 dead rows; 8732 rows in sample, 133407 estimated total rows
ANALYZE

自动吸尘器不工作。这篇文章指出了潜在的问题。。。
https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/
原始线程
我有一张140k行的桌子，每周大约增长500行。
几周前，我调查了表上的查询，发现所有查询都很慢。例如，select count（）耗时6秒。我用pg_repack重新构建了这个表，并假设它已经结束了。我注意到今天的表又慢了，select count（）需要3秒。
数据库中有138个表，只有另一个表（有130万行）需要一秒钟以上的时间来执行select count（*）。
我想知道是否存在损坏，这是否是Postgres中的一个bug，或者是否存在优化问题。
问询处
以下是通过psql（今天）进行的计数

# select count(*) from payslip;
 count  
--------
 140327
(1 row)

Time: 3255.772 ms (00:03.256)

这是查询计划

# explain select count(*) from payslip;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Aggregate  (cost=142820.48..142820.49 rows=1 width=8)
   ->  Bitmap Heap Scan on payslip  (cost=22543.92..142479.77 rows=136285 width=0)
         ->  Bitmap Index Scan on payslip_idx3  (cost=0.00..22509.84 rows=136285 width=0)
(3 rows)

这是数据模型（已截断）。

                         Table "public.payslip"
          Column          |          Type          | Collation | Nullable |                   Default                    
--------------------------+------------------------+-----------+----------+----------------------------------------------
 taxregno                 | character varying(20)  |           | not null | 
 worksid                  | character varying(8)   |           | not null | 
 cutoffdate               | character(10)          |           | not null | 
 productionid             | integer                |           | not null | 
... 

Ignore 50 columns

Indexes:
    "payslip_pkey" PRIMARY KEY, btree (taxregno, worksid, cutoffdate, productionid)
    "payslip_k1" UNIQUE, btree (taxregno, worksid, cutoffdate, productionid)
    "payslip_idx3" btree (worksid)
    "payslip_idx4" btree (ppsnumber)

Postgres版本目前是11。这个数据库库从Postgres8迁移到当前版本超过了10年。我只是按照各种Ubuntu升级的说明操作。

$ psql -V
psql (PostgreSQL) 11.3 (Ubuntu 11.3-1.pgdg14.04+1)

服务器运行在带有SSD存储的Linode linux机器上。我将postgresql.conf页面成本设置为反映SSD。

#seq_page_cost = 1.0            # measured on an arbitrary scale
random_page_cost = 1.0          # same scale as above

今天
不幸的是，这是一个生产服务器，我需要解决性能问题短期内。因此，我现在再次运行pg_repack。
包装后重新包装

# select count(*) from payslip;
 count  
--------
 140327
(1 row)

Time: 26.216 ms

# explain select count(*) from payslip;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Aggregate  (cost=10974.09..10974.10 rows=1 width=8)
   ->  Seq Scan on payslip  (cost=0.00..10623.27 rows=140327 width=0)
(2 rows)

根据一匹没有名字的马的要求，以下是进一步的信息。如上所述，这与重建后的表背道而驰。

# explain (analyze, buffers, timing) select count(*) from payslip;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=12850.75..12850.76 rows=1 width=8) (actual time=42.070..42.071 rows=1 loops=1)
   Buffers: shared hit=11022
   ->  Seq Scan on payslip  (cost=0.00..12485.00 rows=146300 width=0) (actual time=0.010..31.669 rows=140327 loops=1)
         Buffers: shared hit=11022
 Planning Time: 0.102 ms
 Execution Time: 42.115 ms
(6 rows)

一周后更新。
这一周很平静。这张桌子增加了250行。选择count（*）slowed from.04 seconds to.7 seconds。查询从较快的顺序扫描改回较慢的位图索引扫描。

select count(*) from payslip;
 140572

Time: 643.144 ms

这是细节。

explain (analyze, buffers, timing) select count(*) from payslip;
 Aggregate  (cost=108251.57..108251.58 rows=1 width=8) (actual time=718.015..718.016 rows=1 loops=1)
   Buffers: shared hit=169407
   ->  Bitmap Heap Scan on payslip  (cost=8522.42..107900.14 rows=140572 width=0) (actual time=229.612..707.319 rows=140572 loops=1)
         Heap Blocks: exact=76839 lossy=84802
         Buffers: shared hit=169407
         ->  Bitmap Index Scan on payslip_idx3  (cost=0.00..8487.28 rows=140572 width=0) (actual time=205.228..205.228 rows=2212168 loops=1)
               Buffers: shared hit=7757
 Planning Time: 0.115 ms
 Execution Time: 718.069 ms

两周后更新
我重建桌子已经两周了。这周桌子增加了340行。选择计数（*）时间从.6秒减少到2秒。

select count(*) from payslip;
 count  
--------
 140914
(1 row)

Time: 2077.577 ms (00:02.078)

查询计划没有变化，执行速度慢得多。

explain (analyze, buffers, timing) select count(*) from payslip;
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=138089.00..138089.01 rows=1 width=8) (actual time=2068.305..2068.305 rows=1 loops=1)
   Buffers: shared hit=8 read=324086 written=1
   ->  Bitmap Heap Scan on payslip  (cost=17071.92..137736.72 rows=140914 width=0) (actual time=270.512..2056.755 rows=140914 loops=1)
         Heap Blocks: exact=8198 lossy=301091
         Buffers: shared hit=8 read=324086 written=1
         ->  Bitmap Index Scan on payslip_idx3  (cost=0.00..17036.69 rows=140914 width=0) (actual time=268.801..268.801 rows=4223367 loops=1)
               Buffers: shared read=14794
 Planning Time: 0.164 ms
 Execution Time: 2068.623 ms
(9 rows)

Time: 2069.567 ms (00:02.070)

选择的索引（idx3）是一个重复索引，140k条记录中有22k个唯一值。位图索引扫描表明，本周扫描了400万行（在400次插入之后），上周扫描了200万行进行相同的查询，这与性能下降是一致的。
来自索引维护查询的信息（由richyen建议）

 relname | rows_in_bytes | num_rows | number_of_indexes | unique | single_column | multi_column 
---------+---------------+----------+-------------------+--------+---------------+--------------
 payslip | 138 kB        |   140914 |                 4 | Y      |             2 |            2



 schemaname | tablename |  indexname   | num_rows | table_size | index_size | unique | number_of_scans | tuples_read | tuples_fetched 
------------+-----------+--------------+----------+------------+------------+--------+-----------------+-------------+----------------
 public     | payslip   | payslip_k1   |   140914 | 2420 MB    | 244 MB     | Y      |           39720 |  3292501603 |       14295183
 public     | payslip   | payslip_idx4 |   140914 | 2420 MB    | 156 MB     | N      |           43013 |  9529447977 |       34943724
 public     | payslip   | payslip_idx3 |   140914 | 2420 MB    | 116 MB     | N      |           42812 |  3067603558 |       72358879
 public     | payslip   | payslip_pkey |   140914 | 2420 MB    | 244 MB     | Y      |            3540 |   203676311 |        4213496
(4 rows)


  size   |             idx1             |              idx2               |         idx3         | idx4 
---------+------------------------------+---------------------------------+----------------------+------
 488 MB  | payslip_pkey                 | payslip_k1                      |                      | 

在这个阶段，我重新设计了表索引。我将主键设置为序列的整数值，并将序列号包含在所有索引中以使它们唯一。
由于索引已重新生成，选择计数（*）已返回到执行顺序扫描。我将不得不等待表稍微增长一点，看看查询是否使数百万行读取。

explain (analyze, buffers, timing) select count(*) from payslip;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=312850.42..312850.43 rows=1 width=8) (actual time=1348.241..1348.242 rows=1 loops=1)
   Buffers: shared hit=199941 read=111148
   ->  Seq Scan on payslip  (cost=0.00..312498.14 rows=140914 width=0) (actual time=209.227..1336.035 rows=140914 loops=1)
         Buffers: shared hit=199941 read=111148
 Planning Time: 0.069 ms
 Execution Time: 1348.289 ms
(6 rows)

索引信息现在是

 schemaname | tablename |  indexname   | num_rows | table_size | index_size | unique | number_of_scans | tuples_read | tuples_fetched 
------------+-----------+--------------+----------+------------+------------+--------+-----------------+-------------+----------------
 public     | payslip   | payslip_pkey |   140914 | 2430 MB    | 91 MB      | Y      |               0 |           0 |              0
 public     | payslip   | payslip_idx2 |   140914 | 2430 MB    | 202 MB     | Y      |               0 |           0 |              0
 public     | payslip   | payslip_idx4 |   140914 | 2430 MB    | 128 MB     | Y      |               0 |           0 |              0
 public     | payslip   | payslip_idx3 |   140914 | 2430 MB    | 128 MB     | N      |               0 |           0 |              0
(4 rows)

问题解决了
我终于想出了解决办法。我的问题是，我假设pg_repack按照建议的名称重建了表。没有。桌子完全碎了。
出于某种原因，我不知道为什么，对于零碎的表，postgresql决定进行顺序扫描而不是索引扫描。
这就是我应该看的。

# analyse verbose payslip;
INFO:  analyzing "public.payslip"
INFO:  "payslip": scanned 30000 of 458337 pages, containing 8732 live rows and 400621 dead rows; 8732 rows in sample, 133407 estimated total rows
ANALYZE

使用pg_转储并重新加载表，很快解决了问题。
我进一步调查了这个问题，发现了这篇优秀的文章。
https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/
数据库中有两个已准备就绪的事务阻止了autovacuum正常工作。
选择gid、prepared、owner、database、transaction作为xmin
-#从pg_prepared_xacts
-#按年龄（交易）说明排序；
gid |准备|所有者|数据库| xmin
--------------------------------------+-------------------------------+-------+----------+---------
_萨昂4f7780bb6653ccb70ddaf2143ac7a232 | 2019-08-12 13:00:11.738766+01 |凯文|凯文| 1141263
_sa|u 0DB277AEBCB44884763FE6245D702FE | 2019-09-19 14:00:11.977378+01 | kevin | kevin | 2830229
（2行）
感谢大家的帮助。

Answer 1

从上周到本周的变化表明payslip的许多数据不再在缓存中（请参阅hit和read部分中的变化）。
还要注意的是，您的Buffers:越来越Heap Blocks，这意味着您的lossy设置可能太低，无法进行操作。
本周您可能应该将work_mem至少增加到work_mem（因为最新的统计数据表明访问了约309k个页面块）。但是，您可能需要随着表的增长而增加它--25MB可以按会话设置，因此您需要根据基于模式的表大小预测来设置它（我不喜欢这个想法，但也不建议将work_mem设置为任意高，因为全局设置可能导致内存过度分配）
我不太清楚work_mem的内部结构，但我想知道您是否看到了重新打包后性能的提高，因为这些东西存储在内存中，并随着时间的推移被清除掉。
披露：我为EnterpriseDB (EDB)工作