主页 / dba / 问题 / 258503
Accepted
Arun
Asked: 2020-01-31 00:26:39 +0800 CST

Postgres 10 - 查询按顺序减慢

  • 772

我正在运行类似的查询

select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';

在看起来像的桌子上 

                Table "public.students"
          Column       |            Type             |             Modifiers              
    -------------------+-----------------------------+------------------------------------
     id                | uuid                        | not null default gen_random_uuid()
     school_id        | uuid                        | 
Indexes:
    "students_pkey" PRIMARY KEY, btree (id)
    "students_school_id_idx" btree (school_id)

带有 where 的 select 语句的查询计划如下所示 -

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on students  (cost=581.83..83357.10 rows=24954 width=16)
   Recheck Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)
   ->  Bitmap Index Scan on students_school_id_idx  (cost=0.00..575.59 rows=24954 width=0)
         Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

这是相当快的。

现在我们将 order by 添加到带有 id 的查询中，这会降低查询的性能。（这样的查询是由 Rails 生成的，比如 student.first 有一些条件）

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..488.51 rows=1 width=16)
   ->  Index Scan using students_pkey on students  (cost=0.43..12179370.22 rows=24954 width=16)
         Filter: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

如何提高返回此查询结果的速度？目前表中大约有 4990731 条记录，耗时超过 2 分钟！它在带有 db.t2.medium 实例的 RDS 上运行。

更新 运行后Analyze students;

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                       QUERY PLAN                                                    
    -----------------------------------------------------------------------------------------------------------------
     Limit  (cost=8.46..8.46 rows=1 width=16)
       ->  Sort  (cost=8.46..8.46 rows=1 width=16)
             Sort Key: id
             ->  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16)
                   Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

    explain analyze select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                                          QUERY PLAN                                                                         
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=8.46..8.46 rows=1 width=16) (actual time=1.853..1.855 rows=1 loops=1)
     ->  Sort  (cost=8.46..8.46 rows=1 width=16) (actual time=1.851..1.852 rows=1 loops=1)
           Sort Key: id
           Sort Method: quicksort  Memory: 25kB
           ->  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16) (actual time=1.841..1.843 rows=1 loops=1)
                 Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)
    Planning time: 0.145 ms
    Execution time: 1.874 ms
postgresql postgresql-performance

2 个回答

  1. Best Answer

    ORDER BYPostgreSQL 认为，通过按排序顺序扫描行并丢弃行直到找到正确的行，这样会更快地避免对 的排序school_id。

    这可能比预期花费更长的时间有两个原因：

    1. 表统计信息已关闭，PostgreSQL 高估了带有school_id.

      计算新的统计数据，可能具有更高的 值default_statistics_target，以验证这是否是问题所在：

      ANALYZE students;

    2. 正确的（许多）行school_id恰好都有一个相当高的id，因此 PostgreSQL 必须扫描比它讨价还价的更多的行，直到找到匹配项。

      在这种情况下，您应该修改该ORDER BY子句，以便 PostgreSQL 不能使用错误的索引：

      ... ORDER BY id + 0
    • 2

  2. UUID列对性能不利，因为它们通常按定义无序。您命名的列id属于类型UUID，因此可能是无序的。

    当您只运行简单时select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';，查询引擎只需遍历表中的数据 (HEAP) 并忽略与 WHERE 子句 () 不匹配的数据。

    在第二种情况下，您正在做两件事。

    1. students_pkey通过生成有序结果集的索引选择数据，但最终在堆中呈锯齿状。这是它的Index Scan using students_pkey on students (cost=0.43..12179370.22 rows=24954 width=16)一部分EXPLAIN
    2. 根据students_school_id_idx索引过滤第一个结果。这是Filter: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)部分EXPLAIN

    您可能要考虑不使用 UUID，因为它们会带来一些开销。阅读顺序 UUID 生成器文章了解更多信息。

    但也有缺点——与传统的顺序标识符相比，它们可能使访问模式更加随机，导致 WAL 写入放大等。所以让我们看一下生成“顺序”UUID 的扩展，以及它如何减少使用 UUID 的负面影响.

    ...和...

    （强调我的）

    假设我们正在使用 UUID 主键（因此存在唯一索引）将行插入到表中，并且 UUID 生成为随机值。在表中，行可以简单地附加在末尾，这非常便宜。但是指数呢？对于索引排序很重要，因此数据库几乎没有选择插入新项目的位置——它必须进入索引中的特定位置。由于 UUID 值是随机生成的，因此位置将是随机的，所有索引页的分布都是均匀的。

    ...因为...

    （强调我的）

    这是不幸的，因为它不利于自适应缓存管理算法——没有一组“经常”访问的页面可以保存在内存中。如果索引大于内存，那么缓存命中率（对于页面缓存和共享缓冲区）注定会很差。对于小型索引，您可能不太在意。

    表中数据的分布是顺序的，但 UUID 将是无序的。在某些时候，b-tree 索引必须访问数据，并且由于索引被用于ORDER BY通过索引检索数据，实际数据将以锯齿形模式检索。

    这个问题有一些解决方法，但它们要么涉及不同的 UUID 生成，要么涉及对插入性能有影响的聚集索引的使用，因为数据不断地重新排序。

    可以在这里找到对 PostgreSQL 中 B-Tree 索引的一个很好的解释

    基本上在索引的最后一个叶级别发生的事情是这样的：

    LEAF(n)     76a8c180-3a76-492e-b68a-9d980bb50c11 | fec0b6c3-2112-487c-b10f-c515e1a7d1d1
                                               \    /
                                                \  /
                                                 \/ 
                                                 /\
                                                /  \    
                                               /    \
TABLE DATA  fec0b6c3-2112-487c-b10f-c515e1a7d1d1 | 76a8c180-3a76-492e-b68a-9d980bb50c11

    索引是有序的。数据不是。这就是为什么 ODER BY 会由于实际数据的曲折检索而导致开销。

    • 2

相关问题