主页 / user-199982

Arun's questions

Martin Hope
Arun
Asked: 2020-01-31 00:26:39 +0800 CST
  • 5

我正在运行类似的查询

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