主页 / dba / 问题 / 199968
Accepted
Imanol Y.
Asked: 2018-03-13 00:43:44 +0800 CST

ORDER BY 子句会影响查询性能

  • 772

语境：

PostgreSQL 10，users 表有 3667438 条记录，users 表有一个叫做 social 的 JSONB，我们通常使用索引计算函数输出的策略，因此我们可以将信息聚合到单个索引中。函数的输出engagement(social)是双精度数值类型。

问题：

有问题的子句是，该数据还附加了ORDER BY engagement(social) DESC NULLS LAST一个 btree 索引。idx_in_social_engagement with DESC NULLS LAST

快速查询：

EXPLAIN ANALYZE
SELECT  "users".* FROM "users"
WHERE (follower_count(social) < 500000)
AND (engagement(social) > 0.03)
AND (engagement(social) < 0.25)
AND (peemv(social) < 533)
ORDER BY "users"."created_at" ASC
LIMIT 12 OFFSET 0;

Limit  (cost=0.43..52.25 rows=12 width=1333) (actual time=0.113..1.625 
rows=12 loops=1)
   ->  Index Scan using created_at_idx on users  (cost=0.43..7027711.55 rows=1627352 width=1333) (actual time=0.112..1.623 rows=12 loops=1)
         Filter: ((follower_count(social) < 500000) AND (engagement(social) > '0.03'::double precision) AND (engagement(social) <  '0.25'::double precision) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
         Rows Removed by Filter: 8
 Planning time: 0.324 ms
 Execution time: 1.639 ms

慢查询：

EXPLAIN ANALYZE 
SELECT  "users".* FROM "users" 
WHERE (follower_count(social) < 500000) 
AND (engagement(social) > 0.03) 
AND (engagement(social) < 0.25) 
AND (peemv(social) > 0.0) 
AND (peemv(social) < 533) 
ORDER BY engagement(social) DESC NULLS LAST, "users"."created_at" ASC 
LIMIT 12 OFFSET 0;

Limit  (cost=2884438.00..2884438.03 rows=12 width=1341) (actual time=68011.728..68011.730 rows=12 loops=1)
->  Sort  (cost=2884438.00..2888506.38 rows=1627352 width=1341) (actual time=68011.727..68011.728 rows=12 loops=1)
        Sort Key: (engagement(social)) DESC NULLS LAST, created_at
        Sort Method: top-N heapsort  Memory: 45kB
        ->  Index Scan using idx_in_social_engagement on users  (cost=0.43..2847131.26 rows=1627352 width=1341) (actual time=0.082..67019.102 rows=1360633 loops=1)
            Index Cond: ((engagement(social) > '0.03'::double precision) AND (engagement(social) < '0.25'::double precision))
            Filter: ((follower_count(social) < 500000) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
            Rows Removed by Filter: 85580
Planning time: 0.312 ms
Execution time: 68011.752 ms

选择带有 * 因为我需要存储在每一行中的所有数据。

更新：

CREATE INDEX idx_in_social_engagement on influencers USING BTREE ( engagement(social) DESC NULLS LAST)

准确的索引定义

postgresql performance

2 个回答

  1. Best Answer

    你的ORDER BY条款是：

    engagement(social) DESC NULLS LAST, "users"."created_at" ASC

    但我怀疑你的索引只是在：

    engagement(social) DESC NULLS LAST

    所以该指数并不能完全支撑ORDER BY。

    您可以在不使用JSONB或表达式索引的情况下重现相同的问题。您可以通过在ORDER BY.

    如果 PostgreSQL 规划器非常聪明，它可能能够有效地使用现有索引。它必须继续前进，engagement(social) DESC NULLS LAST直到它收集到 12 个满足所有其余过滤器要求的元组。然后它将继续前进该索引，直到它收集engagement(social)与第 12 个元组相关联的所有其余元组（并且满足其他标准）。然后它必须重新排序所有收集到的元组ORDER BY，并将 应用于该LIMIT 12扩展和重新排序的集合。但是 PostgreSQL 规划器并不是无限明智的。

    • 7

  2. 我怀疑这里的罪魁祸首是缺少 JSONB 列的统计信息。Postgres 不保留任何有关 JSONB 列的统计信息，而是使用硬编码的估计值。如果这些估计值相差很远，这很可能会发生，这可能会导致像您的情况一样糟糕的查询计划。

    在您的好计划中，Postgres 首先对数据进行排序，然后对其进行过滤。如果过滤器没有删除很多行，这对于具有 LIMIT 子句和排序列上的索引的查询来说非常快。索引是有序的，因此按顺序排列行非常便宜。limit 子句意味着您只需要获取几行，直到您有足够的数据来满足它。

    但是，如果您的过滤器排除了很多行，例如，如果只有 0.1% 匹配它，那么首先排序将需要您遍历大部分表以找到足够的行，因为您几乎过滤掉了所有行。在这种情况下，首先按索引过滤然后排序要快得多。这就是你糟糕的计划所做的，显然它不适合你的数据。

    到目前为止，最好的选择是将您在此处使用的值放入它们自己的列中。JSONB 对于某些用途非常有用，但如果您不需要灵活性，它提供的普通旧关系方式要好得多。

    功能索引确实提供了统计信息，这应该有助于您的查询。我怀疑在您的情况下，这不起作用，因为您将整个社交列传递给该函数，并且它无法从中建立良好的统计数据。您可以尝试仅在社交列中的参与键上建立索引，这可能会为 Postgres 提供更好的查询计划所需的统计信息。有关如何做到这一点的示例，请参阅我自己关于 JSONB 统计的问题。

    • 1

相关问题