postgresql 在WHERE条件中添加额外OR时，多个订单速度变慢

xxe27gdn 于 2023-05-22 发布在 PostgreSQL

关注(0)|答案(3)|浏览(170)

在PostgreSQL 14数据库中，我有一个交易表，目前包含大约4亿个元素，下面是对它的查询：

SELECT "transactions"."id"
FROM "transactions"
WHERE ("wallet_id" = $1)
ORDER BY "transactions"."id" DESC
LIMIT 10

这工作正常，查询速度也很快。EXPLAIN ANALYZE输出：

Limit  (cost=84991.29..84991.31 rows=10 width=146) (actual time=1.518..1.519 rows=2 loops=1)
  ->  Sort  (cost=84991.29..85056.88 rows=26235 width=146) (actual time=1.517..1.518 rows=2 loops=1)
        Sort Key: id DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using transactions_wallet_id_index on transactions  (cost=0.57..84424.36 rows=26235 width=146) (actual time=1.080..1.497 rows=2 loops=1)
              Index Cond: (wallet_id = $1)
Planning Time: 0.850 ms
Execution Time: 1.682 ms

如果我在where中添加第二个钱包id，那么当两个钱包都有少量交易时（第一个钱包为2，第二个钱包为57），查询需要几分钟：

SELECT "transactions"."id"
FROM "transactions"
WHERE ("wallet_id" = $1 OR "wallet_id" = $2)
ORDER BY "transactions"."id" DESC
LIMIT 10

其分析：

Limit  (cost=0.57..117334.73 rows=10 width=146) (actual time=3683.524..944867.724 rows=10 loops=1)
  ->  Index Scan Backward using transactions_pkey on transactions  (cost=0.57..615664040.26 rows=52471 width=146) (actual time=3683.523..944867.715 rows=10 loops=1)
        Filter: ((wallet_id = $1) OR (wallet_id = $2))
        Rows Removed by Filter: 117937409
Planning Time: 0.810 ms
Execution Time: 944867.797 ms

经过几个小时的调查，问题似乎来自于将ORDER BY与LIMIT结合使用，实际上，如果我删除其中一个，查询运行速度会很快。如果至少有一个钱包有非小数量的交易，它也会运行得很快。id是主键，wallet_id上有一个索引。
这非常令人失望和沮丧。这是我第一次使用Postgres，查询规划器在这样一个简单的查询上做得如此糟糕，这一事实真的很难理解。
我很感激一些关于如何使所有情况下的查询更快的建议。
我现在尝试了几个小时的不同事情，包括在table上运行VACUUM（以防万一）和ANALYZE，但都无济于事。

postgresql

来源：https://stackoverflow.com/questions/76301997/multiple-orders-slower-when-adding-additional-or-to-where-condition

3条答案

按热度按时间

t9eec4r01#

如果您从查询规划器的Angular 来看这个问题，它在这里有两个重要的操作要做：
1.过滤数据
1.分类数据
对于1.，您创建的索引transactions_wallet_id_index更可取。对于2.，主键附带的索引更好（好吧，向后扫描是）。请注意，在过滤掉数据之后，最优查询有一个逻辑Sort操作，而OR的查询没有，它只是有一个限制。
我做了一个200万的table来重现你的场景。

select wallet_id, count(*) from transactions
group by wallet_id
order by wallet_id

钱包ID	count（*）
0	2
1	十二岁
2	四十八
3	一百九十二
四	七六八
5	三零七二
六	一二二八
七	小行星49152
八	196608
九	七八六四三二
十个	九五一四二六

现在，如果我们选择一个非常小的钱包，比如说2和3，它看起来像你所期望的，一个位图或两个条件：

explain analyze select id from transactions where wallet_id = 2 or wallet_id = 3 order by id desc limit 10

Limit  (cost=502.54..502.57 rows=10 width=4) (actual time=0.101..0.104 rows=10 loops=1)
  ->  Sort  (cost=502.54..502.87 rows=133 width=4) (actual time=0.100..0.102 rows=10 loops=1)
        Sort Key: id DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Bitmap Heap Scan on transactions  (cost=9.93..499.67 rows=133 width=4) (actual time=0.033..0.060 rows=240 loops=1)
              Recheck Cond: ((wallet_id = 2) OR (wallet_id = 3))
              Heap Blocks: exact=3
              ->  BitmapOr  (cost=9.93..9.93 rows=133 width=0) (actual time=0.024..0.024 rows=0 loops=1)
                    ->  Bitmap Index Scan on ux1  (cost=0.00..4.44 rows=1 width=0) (actual time=0.014..0.015 rows=48 loops=1)
                          Index Cond: (wallet_id = 2)
                    ->  Bitmap Index Scan on ux1  (cost=0.00..5.42 rows=133 width=0) (actual time=0.009..0.009 rows=192 loops=1)
                          Index Cond: (wallet_id = 3)
Planning Time: 0.097 ms
Execution Time: 0.125 ms

但在某些时候，我们看到了你所看到的，钱包3和4：

explain analyze select id from transactions where wallet_id = 3 or wallet_id = 4 order by id desc limit 10

Limit  (cost=0.43..728.01 rows=10 width=4) (actual time=171.911..171.915 rows=10 loops=1)
  ->  Index Scan Backward using transactions_pkey on transactions  (cost=0.43..72758.43 rows=1000 width=4) (actual time=171.910..171.912 rows=10 loops=1)
        Filter: ((wallet_id = 3) OR (wallet_id = 4))
        Rows Removed by Filter: 999489
Planning Time: 0.095 ms
Execution Time: 171.932 ms

查询规划器只是认为对该集合进行排序比过滤该集合更昂贵。
对于这个查询（我知道），没有真正的解决方法，OR查询在统计数据方面很混乱，相信我，不仅仅是Postgres受到影响。
您可以尝试ANALYZE transactions，但您的统计数据可能是最新的。扩展的统计数据在这里也帮不了你。您可能会调整一些性能设置（特别是work_mem，可能还有random_page_cost）来强制执行正确的计划，但您会遇到其他查询的混乱。
一个可靠的优化方法是自己做。正如我所说，OR查询是有问题的。尝试为您的查询创建一个支持索引，我们可以尝试几个选项。

create index ux1 on transactions (wallet_id, id desc)

这可能会让习惯使用SQL Server的人感到惊讶，但是你可以通过使用IN（在SQL Server中，IN被翻译为一堆ORs）来获得Postgres的不同计划：

explain analyze select id from transactions where wallet_id in (3, 4) order by id desc limit 10

你会得到一个很好的：

Limit  (cost=55.96..55.99 rows=10 width=4) (actual time=0.250..0.252 rows=10 loops=1)
  ->  Sort  (cost=55.96..58.46 rows=1000 width=4) (actual time=0.249..0.250 rows=10 loops=1)
        Sort Key: id DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Index Only Scan using ux1 on transactions  (cost=0.43..34.36 rows=1000 width=4) (actual time=0.022..0.144 rows=960 loops=1)
              Index Cond: (wallet_id = ANY ('{3,4}'::integer[]))
              Heap Fetches: 0
Planning Time: 0.092 ms
Execution Time: 0.268 ms

你也可以尝试通过告诉“这些不是你要找的ID”（或者有一个索引）来扰乱计划者的头脑：

explain analyze select id from transactions where wallet_id = 3 or wallet_id = 4 order by 0+id desc limit 10

(note 0+id - 1*id，id+id也可以，任何使它看起来与现有索引/ pk不同的东西）：

Limit  (cost=3027.11..3027.13 rows=10 width=8) (actual time=0.332..0.334 rows=10 loops=1)
  ->  Sort  (cost=3027.11..3029.61 rows=1000 width=8) (actual time=0.331..0.332 rows=10 loops=1)
        Sort Key: ((1 * id)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Bitmap Heap Scan on transactions  (cost=16.86..3005.50 rows=1000 width=8) (actual time=0.047..0.171 rows=960 loops=1)
              Recheck Cond: ((wallet_id = 3) OR (wallet_id = 4))
              Heap Blocks: exact=7
              ->  BitmapOr  (cost=16.86..16.86 rows=1000 width=0) (actual time=0.037..0.038 rows=0 loops=1)
                    ->  Bitmap Index Scan on ux1  (cost=0.00..5.93 rows=200 width=0) (actual time=0.016..0.016 rows=192 loops=1)
                          Index Cond: (wallet_id = 3)
                    ->  Bitmap Index Scan on ux  (cost=0.00..10.43 rows=800 width=0) (actual time=0.021..0.021 rows=768 loops=1)
                          Index Cond: (wallet_id = 4)
Planning Time: 0.094 ms
Execution Time: 0.355 ms

在某些情况下，您也可以将查询拆分为两个并将它们联合在一起：

explain analyze select id from (select id from transactions where wallet_id = 3 union all select id from transactions where wallet_id = 4) q order by id desc limit 10

Limit  (cost=70.96..70.99 rows=10 width=4) (actual time=0.539..0.542 rows=10 loops=1)
  ->  Sort  (cost=70.96..73.46 rows=1000 width=4) (actual time=0.538..0.540 rows=10 loops=1)
        Sort Key: transactions.id DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Append  (cost=0.43..49.35 rows=1000 width=4) (actual time=0.030..0.349 rows=960 loops=1)
              ->  Index Only Scan using ux1 on transactions  (cost=0.43..7.93 rows=200 width=4) (actual time=0.029..0.065 rows=192 loops=1)
                    Index Cond: (wallet_id = 3)
                    Heap Fetches: 0
              ->  Index Only Scan using ux1 on transactions transactions_1  (cost=0.43..26.43 rows=800 width=4) (actual time=0.012..0.179 rows=768 loops=1)
                    Index Cond: (wallet_id = 4)
                    Heap Fetches: 0
Planning Time: 0.176 ms
Execution Time: 0.569 ms

(this对于更大的钱包可能不太好用）
你有很多选择，IN应该是最好的，但你可能想尝试所有这些，看看哪一个产生最好的结果。

赞(0）回复(0）举报 2023-05-22

vltsax252#

为什么？
额外的过滤器使Postgres切换到不同的查询计划，结果证明效率极低。这种效果或多或少是偶然触发的。潜在的问题是Postgres严重低估了WHERE子句的选择性。它期望它可以按照请求的排序顺序遍历PK（transactions_pkey）上的索引，并很快找到几行（LIMIT 10）。事实证明，过滤器是非常有选择性的，Postgres必须跳过超过1.18亿行（！！！）才能找到足够的匹配（Rows Removed by Filter: 117937409）。如果没有足够的行来满足限制，那么在Postgres最终给予之前，必须访问所有行。最坏的情况。
该决定是基于错误或误导性列统计数据作出的。如果您可以改进列统计信息，那么可能会自行解决这个问题。* 可能 * 像ANALYZE transactions;一样简单。有各种各样的方法。以下是昨天dba.SE上的一个相关回答，还有更多关于这个问题的内容（以及更多类似案例的链接）：

Simple query with two WHERE clauses uses inferior index scan

碰巧的是，我用ORDER BY id + 0讨论了同样的“蛮力”破解，你找到了你的答案。

解决方案

对于您的特殊情况，也有不同的方法。要获得最佳性能，请创建多列索引（一次）：

CREATE INDEX transactions_wallet_id_id_idx ON transactions (wallet_id, id DESC);

也大大提高了简单查询的性能。（即使这已经相当快了。）您将看到一个没有“排序”步骤的仅索引扫描（或至少是索引扫描）。
然后使用此查询：

(
SELECT id
FROM   transactions
WHERE  wallet_id = $1
ORDER  BY id DESC
LIMIT  10
)
UNION ALL
(
SELECT id
FROM   transactions
WHERE  wallet_id = $2
ORDER  BY id DESC
LIMIT  10
)
ORDER BY id DESC
LIMIT 10;

所有括号均为必填项。
现在，第二个查询的成本大约是第一个查询的两倍，即非常快

赞(0）回复(0）举报 2023-05-22

sauutmhj3#

经过进一步的挖掘，我终于自己解决了这个问题，这要感谢this blog post。它只适用于ORDER BY列是整数（或者可能只是一个数字），通过将+ 0添加到它。把它贴出来以防它能帮到别人。
我还发现了几个报告给PostgreSQL的问题，这些问题与这个问题类似或相关，我发现一些核心开发人员对一些现实世界的使用问题的漠视令人难以置信。显然，他们找到了一个可以接受的设计决策，即通过在整数字段上将+ 0添加到ORDER BY，在0.3秒内运行的查询可能需要20分钟，否则由于规划器中的一些早期中止功能出错。
这让我认真地重新考虑了我们迁移到Postgres的决定，我们已经在考虑替代方案。真的很伤心

赞(0）回复(0）举报 2023-05-22

我来回答

postgresql 在WHERE条件中添加额外OR时，多个订单速度变慢

3条答案

解决方案

相关问题

热门标签

最新问答