PostgreSQL 查询需要 100 倍的时间，当我使 WHERE 子句更具限制性时，这是违反直觉的

Question

我有一个PostgreSQL查询，需要超过15秒。当我更改 WHERE 子句中的某些值时，查询速度提高了 100 倍以上，即使更改扩大了查询的范围也是如此。

查询 #1 需要 15.2秒：SELECT p.category_id, count(*) FROM cr JOIN p ON p_id = p.id WHERE p.category_id = 48 AND cr.created_date > '2019-08-23 21:00:00 +0000' GROUP BY p.category_id;

查询 #2 需要 44毫秒：SELECT p.category_id, count(*) FROM cr JOIN p ON p_id = p.id WHERE p.category_id = 48 AND cr.created_date > '2017-08-23 21:00:00 +0000' GROUP BY p.category_id;唯一的更改是created_date，我在其中又包含了两年的cr行，但查询速度非常快。

查询 #3 需要 292毫秒：SELECT p.category_id, count(*) FROM cr JOIN p ON p_id = p.id WHERE p.category_id < 49 AND cr.created_date > '2019-08-23 21:00:00 +0000' GROUP BY p.category_id;。查询 #1 的唯一变化是category_id，我在其中添加了 47 个可能的 id(1-48 而不是 48(，但查询仍然比原始查询快得多。

我不知所措。查询 #2 和 #3 应该更耗时，但这只是第一个需要很长时间

的查询。编辑：

这是我使用查询 #1EXPLAIN (ANALYZE, BUFFERS) SELECT ...时的输出：

GroupAggregate  (cost=289.67..301.73 rows=1 width=12) (actual time=19075.641..19075.642 rows=1 loops=1)
Group Key: p.category_id
Buffers: shared hit=606998
->  Nested Loop  (cost=289.67..301.71 rows=1 width=4) (actual time=10.354..19069.554 rows=14609 loops=1)
Buffers: shared hit=606998
->  Index Scan using _idx_category_id on p  (cost=0.42..8.44 rows=1 width=8) (actual time=0.018..3.676 rows=2642 loops=1)
Index Cond: (category_id = 48)
Buffers: shared hit=544
->  Bitmap Heap Scan on cr  (cost=289.25..293.26 rows=1 width=4) (actual time=7.203..7.211 rows=6 loops=2642)
Recheck Cond: ((p_id = p.id) AND (created_date > '2019-08-23 21:00:00+00'::timestamp with time zone))
Heap Blocks: exact=14591
Buffers: shared hit=606454
->  BitmapAnd  (cost=289.25..289.25 rows=1 width=0) (actual time=7.199..7.199 rows=0 loops=2642)
Buffers: shared hit=591863
->  Bitmap Index Scan on cr_p_id_index  (cost=0.00..9.88 rows=193 width=0) (actual time=0.006..0.006 rows=6 loops=2642)
Index Cond: (p_id = p.id)
Buffers: shared hit=7981
->  Bitmap Index Scan on cr_created_date_index  (cost=0.00..276.71 rows=9637 width=0) (actual time=7.189..7.189 rows=79774 loops=2642)
Index Cond: (created_date > '2019-08-23 21:00:00+00'::timestamp with time zone)
Buffers: shared hit=583882
Planning time: 0.431 ms
Execution time: 19075.698 ms

这里是查询#2：

GroupAggregate  (cost=0.85..720.03 rows=1 width=12) (actual time=31.628..31.630 rows=1 loops=1)
Group Key: p.category_id
Buffers: shared hit=24780
->  Nested Loop  (cost=0.85..719.92 rows=20 width=4) (actual time=0.024..27.561 rows=16281 loops=1)
Buffers: shared hit=24780
->  Index Scan using _idx_category_id on p  (cost=0.42..8.44 rows=1 width=8) (actual time=0.015..1.883 rows=2642 loops=1)
Index Cond: (category_id = 48)
Buffers: shared hit=544
->  Index Scan using cr_p_id_index on cr  (cost=0.43..709.55 rows=193 width=4) (actual time=0.002..0.007 rows=6 loops=2642)
Index Cond: (p_id = p.id)
Filter: (created_date > '2017-08-23 21:00:00+00'::timestamp with time zone)
Buffers: shared hit=24236
Planning time: 0.251 ms
Execution time: 31.663 ms

这里是查询 #3：

HashAggregate  (cost=52583.40..52583.67 rows=27 width=12) (actual time=389.141..389.150 rows=22 loops=1)
Group Key: p.category_id
Buffers: shared hit=89513, temp read=1187 written=1173
->  Hash Join  (cost=20169.93..52535.14 rows=9651 width=4) (actual time=235.938..363.334 rows=79992 loops=1)
Hash Cond: (cr.p_id = p.id)
Buffers: shared hit=89513, temp read=1187 written=1173
->  Index Scan using cr_created_date_index on cr  (cost=0.43..30914.94 rows=9651 width=4) (actual time=0.010..50.387 rows=79992 loops=1)
Index Cond: (created_date > '2019-08-23 21:00:00+00'::timestamp with time zone)
Buffers: shared hit=78529
->  Hash  (cost=14955.75..14955.75 rows=317740 width=8) (actual time=235.394..235.394 rows=321138 loops=1)
Buckets: 131072  Batches: 8  Memory Usage: 2608kB
Buffers: shared hit=10984, temp written=957
->  Seq Scan on p  (cost=0.00..14955.75 rows=317740 width=8) (actual time=0.010..127.499 rows=321138 loops=1)
Filter: (category_id < 49)
Rows Removed by Filter: 3
Buffers: shared hit=10984
Planning time: 0.241 ms
Execution time: 389.209 ms

Answer 1

>

->  Index Scan using _idx_category_id on p  (cost=0.42..8.44 rows=1 width=8) (actual time=0.015..1.883 rows=2642 loops=1)

当估计的行数偏离系数 2642 时，就会发生错误的计划。

如果您分析表(ANALYZE P;(，是否可以解决问题？ 如果是这样，那么你不得不想知道为什么自动真空系统没有对其进行自动分析。

Answer 2

您看到这种差异的原因有很多。以下是几个示例。

1. 如果多次运行相同的查询，则可能会从缓存中给出结果。因此，请在查询和检查中进行轻微更改(至少在查询中使用的日期值中添加/删除几秒钟(。
2. 匹配的行数将增加所花费的时间。

您可以在 postgres 中通过explain方法检查性能。它将返回适当的结果，您需要通过此解释方法比较成本回报。这是比较查询结果的最佳方式。

参考： https://www.postgresql.org/docs/9.3/sql-explain.html

您可以很快通过以下方式改进查询。

1. 根据您使用的列类型 (date, string( 和运算符 (=, like, ~=( 添加适当的索引。Postgres 有不同的索引，您可以根据情况使用这些索引。如果要在索引中使用日期类型列，请使用专门用于日期相关查询brin索引。如果要检查 或likeequality，请使用btree索引。如果您没有添加索引，那么它将在您查询时搜索整个表。当您添加索引时，它将通过基于适当索引的搜索来固定。

2. 尝试使用批处理。同时获取特定的行数(1000 行(，而不是同时加载所有记录。如果同时加载所有记录 然后它将创建与内存相关的问题，并且加载所有数据需要很长时间。

3. 根据您的计算机配置，增加您的 postgres 默认配置，如以下页面中的建议。它还将帮助您提高性能。

https://pgtune.leopard.in.ua/#/