postgresql - 解释 postgres 查询，为什么使用 WHERE 和 LIMIT 的查询要长得多

Question

我使用的是 postgres v9.6.5。我有一个看起来并不那么复杂的查询，想知道为什么它这么“慢”(它并不是真的那么慢，但实际上我没有很多数据 - 比如几千行)。

这里是查询:

SELECT o0.* 
FROM "orders" AS o0 
JOIN "balances" AS b1 ON b1."id" = o0."balance_id" 
JOIN "users" AS u3 ON u3."id" = b1."user_id" 
WHERE (u3."partner_id" = 3) 
ORDER BY o0."id" DESC LIMIT 10;

那是查询计划:

Limit  (cost=0.43..12.84 rows=10 width=148) (actual time=0.062..53.866 rows=4 loops=1)
  ->  Nested Loop  (cost=0.43..4750.03 rows=3826 width=148) (actual time=0.061..53.864 rows=4 loops=1)
        Join Filter: (b1.user_id = u3.id)
        Rows Removed by Join Filter: 67404
        ->  Nested Loop  (cost=0.43..3945.32 rows=17856 width=152) (actual time=0.025..38.457 rows=16852 loops=1)
              ->  Index Scan Backward using orders_pkey on orders o0  (cost=0.29..897.80 rows=17856 width=148) (actual time=0.016..11.558 rows=16852 loops=1)
              ->  Index Scan using balances_pkey on balances b1  (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=16852)
                    Index Cond: (id = o0.balance_id)
        ->  Materialize  (cost=0.00..1.19 rows=3 width=4) (actual time=0.000..0.000 rows=4 loops=16852)
              ->  Seq Scan on users u3  (cost=0.00..1.18 rows=3 width=4) (actual time=0.023..0.030 rows=4 loops=1)
                    Filter: (partner_id = 3)
                    Rows Removed by Filter: 12
Planning time: 0.780 ms
Execution time: 54.053 ms

我实际上在没有 LIMIT 的情况下进行了尝试，但我得到了完全不同的计划:

Sort  (cost=874.23..883.80 rows=3826 width=148) (actual time=11.361..11.362 rows=4 loops=1)
  Sort Key: o0.id DESC
  Sort Method: quicksort  Memory: 26kB
  ->  Hash Join  (cost=3.77..646.55 rows=3826 width=148) (actual time=11.300..11.346 rows=4 loops=1)
        Hash Cond: (o0.balance_id = b1.id)
        ->  Seq Scan on orders o0  (cost=0.00..537.56 rows=17856 width=148) (actual time=0.012..8.464 rows=16852 loops=1)
        ->  Hash  (cost=3.55..3.55 rows=18 width=4) (actual time=0.125..0.125 rows=24 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              ->  Hash Join  (cost=1.21..3.55 rows=18 width=4) (actual time=0.046..0.089 rows=24 loops=1)
                    Hash Cond: (b1.user_id = u3.id)
                    ->  Seq Scan on balances b1  (cost=0.00..1.84 rows=84 width=8) (actual time=0.011..0.029 rows=96 loops=1)
                    ->  Hash  (cost=1.18..1.18 rows=3 width=4) (actual time=0.028..0.028 rows=4 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
                          ->  Seq Scan on users u3  (cost=0.00..1.18 rows=3 width=4) (actual time=0.014..0.021 rows=4 loops=1)
                                Filter: (partner_id = 3)
                                Rows Removed by Filter: 12
Planning time: 0.569 ms
Execution time: 11.420 ms

同样没有WHERE(但有LIMIT):

Limit  (cost=0.43..4.74 rows=10 width=148) (actual time=0.023..0.066 rows=10 loops=1)
  ->  Nested Loop  (cost=0.43..7696.26 rows=17856 width=148) (actual time=0.022..0.065 rows=10 loops=1)
        Join Filter: (b1.user_id = u3.id)
        Rows Removed by Join Filter: 139
        ->  Nested Loop  (cost=0.43..3945.32 rows=17856 width=152) (actual time=0.009..0.029 rows=10 loops=1)
              ->  Index Scan Backward using orders_pkey on orders o0  (cost=0.29..897.80 rows=17856 width=148) (actual time=0.007..0.015 rows=10 loops=1)
              ->  Index Scan using balances_pkey on balances b1  (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=10)
                    Index Cond: (id = o0.balance_id)
        ->  Materialize  (cost=0.00..1.21 rows=14 width=4) (actual time=0.001..0.001 rows=15 loops=10)
              ->  Seq Scan on users u3  (cost=0.00..1.14 rows=14 width=4) (actual time=0.005..0.007 rows=16 loops=1)
Planning time: 0.286 ms
Execution time: 0.097 ms

如您所见，没有 WHERE 它会快得多。有人可以向我提供一些信息吗？我在哪里可以找到这些计划的解释以更好地理解它们？还有我该怎么做才能使这些查询更快(或者我不应该担心，因为数据增加了 100 倍，它们仍然足够快？- 50 毫秒对我来说已经足够了)

Answer 1

PostgreSQL 认为如果它以正确的顺序扫描 orders 直到找到满足 WHERE 的匹配 users 条目，它将是最快的条件。

但是，数据分布似乎必须扫描近 17000 个订单才能找到匹配项。

由于 PostgreSQL 不知道值如何跨表关联，因此您无能为力。

您可以强制 PostgreSQL 在没有 LIMIT 子句的情况下计划查询，如下所示:

SELECT *
FROM (<your query without ORDER BY and LIMIT> OFFSET 0) q
ORDER BY id DESC LIMIT 10;

对于前 N 排序，这应该表现得更好。