devbox
2023面试高手
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

PostgreSQL 优化器案例之 - order by limit 索引选择问题

作者：2023面试高手 | 2024-02-23 08:54:12

赞

踩

postgres order by 优化

标签

PostgreSQL , limit , order by , 优化器 , 选择性 , 相关性 , 数据存储顺序 , 目标数据存储顺序

背景

当我们在执行一个这样的SQL时，假如有这样几个索引(c1,c2) (id)，数据库到底该用哪个索引呢？

  1. explain select * from tbl where c1=200 and c2=200 order by id limit 10;  
  2. 或  
  3. explain select * from tbl where c1=200 and c2 between 100 and 300 order by id limit 10;

实际上PG会通过计算成本得到应该使用哪个索引。

但是实际上计算公式本身的通用性，使得当C1，C2数据分布倾斜时，可能导致某些C1，C2输入值的执行计划不准确。

走c1,c2索引，当满足c1,c2条件的记录很少时。如果数据比较多，则SORT耗费的成本就较大，导致整个耗时变大。

走id索引，当满足c1,c2条件的记录很多时，（并且c1,c2满足条件的数据分布在id偏小的端），如果分布在大端，则需要扫描更多的记录才能找到满足条件的记录。

如果数据库统计信息中可以评估出来满足条件的c1,c2的记录与ID顺序分布的关系，也可以作为优化器计算成本的一种参考输入（当然要做到这个可能比较复杂，同时也会增加优化器计算成本的开销）。

下面通过实例来说明。

例子

1、建测试表

  1. postgres=# create table tbl (id int, c1 int, c2 int, c3 int, c4 int);  
  2. CREATE TABLE

2、写入一批随机数据，ID从1到1000万。

  1. postgres=# insert into tbl select generate_series(1,10000000), random()*100, random()*100, random()*100, random()*100;  
  2. INSERT 0 10000000

3、写入另一批100万条数据，c1,c2 与前面1000万的值不一样。

  1. postgres=# insert into tbl select generate_series(10000001,11000000), 200,200,200,200;  
  2. INSERT 0 1000000

4、创建两个索引，也就是本文需要重点关注的，到底走哪个索引更划算

  1. postgres=# create index idx_tbl_1 on tbl(id);  
  2. CREATE INDEX  
  3. postgres=# create index idx_tbl_2 on tbl(c1,c2,c3,c4);  
  4. CREATE INDEX

5、收集统计信息

  1. postgres=# vacuum analyze tbl;  
  2. VACUUM

6、查看下面SQL的执行计划，走了id索引

  1. postgres=# explain select * from tbl where c1=200 and c2=200 order by id limit 10;  
  2.                                       QUERY PLAN                                        
  3. --------------------------------------------------------------------------------------  
  4.  Limit  (cost=0.43..32.59 rows=10 width=20)  
  5.    ->  Index Scan using idx_tbl_1 on tbl  (cost=0.43..323244.26 rows=100533 width=20)  
  6.          Filter: ((c1 = 200) AND (c2 = 200))  
  7. (3 rows)

还是走了id索引

  1. postgres=# explain select * from tbl where c1=200 and c2=200 order by id limit 1000;  
  2.                                       QUERY PLAN                                        
  3. --------------------------------------------------------------------------------------  
  4.  Limit  (cost=0.43..3215.74 rows=1000 width=20)  
  5.    ->  Index Scan using idx_tbl_1 on tbl  (cost=0.43..323244.26 rows=100533 width=20)  
  6.          Filter: ((c1 = 200) AND (c2 = 200))  
  7. (3 rows)

当LIMIT达到50000时，走了c1,c2的索引。为什么呢？

  1. postgres=# explain select * from tbl where c1=200 and c2=200 order by id limit 50000;  
  2.                                          QUERY PLAN                                           
  3. --------------------------------------------------------------------------------------------  
  4.  Limit  (cost=70355.06..70480.06 rows=50000 width=20)  
  5.    ->  Sort  (cost=70355.06..70606.39 rows=100533 width=20)  
  6.          Sort Key: id  
  7.          ->  Bitmap Heap Scan on tbl  (cost=1457.82..62005.97 rows=100533 width=20)  
  8.                Recheck Cond: ((c1 = 200) AND (c2 = 200))  
  9.                ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..1432.69 rows=100533 width=0)  
  10.                      Index Cond: ((c1 = 200) AND (c2 = 200))  
  11. (7 rows)

7、分析以上执行计划的含义

首先，表的记录数(1100万)除以"满足c1=200 and c2=200 条件的记录数"(100533)，得到平均需要扫描多少条记录，可以得到一条满足c1=200 and c2=200条件的记录.

  1. postgres=# select 11000000/100533.0;  
  2.        ?column?         
  3. ----------------------  
  4.  109.4168084111684720  
  5. (1 row)

也就是说每扫描109.4条记录，可以得到一条满足条件的记录。（优化器这么算，是认为数据分布是均匀的。）

但是，实际上，数据分布是不均匀的，c1=200 and c2=200的记录在表的末端（1000万条记录后面），也就是说需要扫描1000万条记录后，才能得到1条满足c1=200 and c2=200的记录。

并不是估算的每扫描109.4条记录，可以得到一条满足条件的记录。

问题就出在这里。

8、我们再来分析一下为什么limit 50000时，选择了c1,c2的索引。而不是id的索引

使用ID索引时，需要扫描100533条记录，同时需要排序，直到排序完成，总成约70606.39。然后就是GET HEAP TUPLE的成本。

当使用id的索引扫描时，返回多少条记录能达到70606.39的成本呢？

  1. 以limit 1000的3215.74成本为例  
  2.   
  3. postgres=# select 70606.39/3215.74;  
  4.       ?column?         
  5. ---------------------  
  6.  21.9564983487471002  
  7. (1 row)  
  8.   
  9. postgres=# select 21.956*1000;  
  10.  ?column?    
  11. -----------  
  12.  21956.000  
  13. (1 row)

分水岭

经过以上分析，也就是说，LIMIT 21956时，走ID索引扫描的执行计划，成本可达到70606.39。

所以limit 21956是一个分水岭，大于这个值时，可能使用c1,c2的索引扫描，而小于它，则会使用ID索引扫描.

如下

  1. postgres=# explain select * from tbl where c1=200 and c2=200 order by id limit 22000;  
  2.                                          QUERY PLAN                                           
  3. --------------------------------------------------------------------------------------------  
  4.  Limit  (cost=69759.69..69814.69 rows=22000 width=20)  
  5.    ->  Sort  (cost=69759.69..70011.02 rows=100533 width=20)  
  6.          Sort Key: id  
  7.          ->  Bitmap Heap Scan on tbl  (cost=1457.82..62005.97 rows=100533 width=20)  
  8.                Recheck Cond: ((c1 = 200) AND (c2 = 200))  
  9.                ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..1432.69 rows=100533 width=0)  
  10.                      Index Cond: ((c1 = 200) AND (c2 = 200))  
  11. (7 rows)  
  1. postgres=# explain select * from tbl where c1=200 and c2=200 order by id limit 21000;  
  2.                                       QUERY PLAN                                        
  3. --------------------------------------------------------------------------------------  
  4.  Limit  (cost=0.43..67521.75 rows=21000 width=20)  
  5.    ->  Index Scan using idx_tbl_1 on tbl  (cost=0.43..323244.26 rows=100533 width=20)  
  6.          Filter: ((c1 = 200) AND (c2 = 200))  
  7. (3 rows)

真实的执行耗时

很显然，使用id扫描，一定会慢，因为满足条件的数据都分布在1000万行后面。

1、c1,c2索引扫描，直接命中数据，加排序（100万条），略快。

  1. postgres=# explain  analyze select * from tbl where c1=200 and c2=200 order by id limit 22000;  
  2.                                                                   QUERY PLAN                                                                    
  3. ----------------------------------------------------------------------------------------------------------------------------------------------  
  4.  Limit  (cost=69759.69..69814.69 rows=22000 width=20) (actual time=293.961..299.054 rows=22000 loops=1)  
  5.    ->  Sort  (cost=69759.69..70011.02 rows=100533 width=20) (actual time=293.960..296.006 rows=22000 loops=1)  
  6.          Sort Key: id  
  7.          Sort Method: top-N heapsort  Memory: 3255kB  
  8.          ->  Bitmap Heap Scan on tbl  (cost=1457.82..62005.97 rows=100533 width=20) (actual time=47.919..175.698 rows=1000000 loops=1)  
  9.                Recheck Cond: ((c1 = 200) AND (c2 = 200))  
  10.                Heap Blocks: exact=6370  
  11.                ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..1432.69 rows=100533 width=0) (actual time=47.160..47.160 rows=1000000 loops=1)  
  12.                      Index Cond: ((c1 = 200) AND (c2 = 200))  
  13.  Planning time: 0.152 ms  
  14.  Execution time: 300.664 ms  
  15. (11 rows)

2、id 索引扫描，慢。

  1. postgres=# explain  analyze select * from tbl where c1=200 and c2=200 order by id limit 21000;  
  2.                                                                 QUERY PLAN                                                                  
  3. ------------------------------------------------------------------------------------------------------------------------------------------  
  4.  Limit  (cost=0.43..67521.75 rows=21000 width=20) (actual time=1404.932..1412.594 rows=21000 loops=1)  
  5.    ->  Index Scan using idx_tbl_1 on tbl  (cost=0.43..323244.26 rows=100533 width=20) (actual time=1404.930..1409.639 rows=21000 loops=1)  
  6.          Filter: ((c1 = 200) AND (c2 = 200))  
  7.          Rows Removed by Filter: 10000000  
  8.  Planning time: 0.139 ms  
  9.  Execution time: 1414.142 ms  
  10. (6 rows)

3、limit 10同样，id 索引扫描，慢。

  1. postgres=# explain ( analyze,verbose,timing,costs,buffers) select * from tbl where c1=200 and c2=200 order by id limit 10;  
  2.                                                                   QUERY PLAN                                                                    
  3. ----------------------------------------------------------------------------------------------------------------------------------------------  
  4.  Limit  (cost=0.43..32.59 rows=10 width=20) (actual time=1403.861..1403.865 rows=10 loops=1)  
  5.    Output: id, c1, c2, c3, c4  
  6.    Buffers: shared hit=91020  
  7.    ->  Index Scan using idx_tbl_1 on public.tbl  (cost=0.43..323244.26 rows=100533 width=20) (actual time=1403.859..1403.861 rows=10 loops=1)  
  8.          Output: id, c1, c2, c3, c4  
  9.          Filter: ((tbl.c1 = 200) AND (tbl.c2 = 200))  
  10.          Rows Removed by Filter: 10000000  
  11.          Buffers: shared hit=91020  
  12.  Planning time: 0.127 ms  
  13.  Execution time: 1403.893 ms  
  14. (10 rows)

优化方法

1、使用HINT或者改SQL，强制不走ID扫描。

并不适用于所有场景，比如数据分布均匀时，那么PG的这周成本计算方法就对口，那么什么时候使用ID，什么时候使用C1，C2索引扫描就合乎常理了。

  1. postgres=# explain ( analyze,verbose,timing,costs,buffers) select * from tbl where c1=200 and c2=200 order by id+0 limit 10;  
  2.                                                                   QUERY PLAN                                                                    
  3. ----------------------------------------------------------------------------------------------------------------------------------------------  
  4.  Limit  (cost=64429.79..64429.81 rows=10 width=24) (actual time=409.622..409.626 rows=10 loops=1)  
  5.    Output: id, c1, c2, c3, c4, ((id + 0))  
  6.    Buffers: shared hit=10205  
  7.    ->  Sort  (cost=64429.79..64681.12 rows=100533 width=24) (actual time=409.620..409.621 rows=10 loops=1)  
  8.          Output: id, c1, c2, c3, c4, ((id + 0))  
  9.          Sort Key: ((tbl.id + 0))  
  10.          Sort Method: top-N heapsort  Memory: 25kB  
  11.          Buffers: shared hit=10205  
  12.          ->  Bitmap Heap Scan on public.tbl  (cost=1457.82..62257.30 rows=100533 width=24) (actual time=47.347..237.455 rows=1000000 loops=1)  
  13.                Output: id, c1, c2, c3, c4, (id + 0)  
  14.                Recheck Cond: ((tbl.c1 = 200) AND (tbl.c2 = 200))  
  15.                Heap Blocks: exact=6370  
  16.                Buffers: shared hit=10205  
  17.                ->  Bitmap Index Scan on idx_tbl_2  (cost=0.00..1432.69 rows=100533 width=0) (actual time=46.577..46.577 rows=1000000 loops=1)  
  18.                      Index Cond: ((tbl.c1 = 200) AND (tbl.c2 = 200))  
  19.                      Buffers: shared hit=3835  
  20.  Planning time: 0.133 ms  
  21.  Execution time: 409.670 ms  
  22. (18 rows)

2、如果c1,c2是等值查询的话，可以用以下索引，那么效率是最高的。

  1. postgres=# create index idx_tbl_3 on tbl(c1,c2,id);  
  2. CREATE INDEX  
  3.   
  4.   
  5. postgres=# explain ( analyze,verbose,timing,costs,buffers) select * from tbl where c1=200 and c2 =200 order by id limit 10;  
  6.                                                               QUERY PLAN                                                                 
  7. ---------------------------------------------------------------------------------------------------------------------------------------  
  8.  Limit  (cost=0.56..6.93 rows=10 width=20) (actual time=0.102..0.106 rows=10 loops=1)  
  9.    Output: id, c1, c2, c3, c4  
  10.    Buffers: shared hit=1 read=4  
  11.    I/O Timings: read=0.047  
  12.    ->  Index Scan using idx_tbl_3 on public.tbl  (cost=0.56..64086.79 rows=100533 width=20) (actual time=0.101..0.103 rows=10 loops=1)  
  13.          Output: id, c1, c2, c3, c4  
  14.          Index Cond: ((tbl.c1 = 200) AND (tbl.c2 = 200))  
  15.          Buffers: shared hit=1 read=4  
  16.          I/O Timings: read=0.047  
  17.  Planning time: 0.142 ms  
  18.  Execution time: 0.131 ms  
  19. (11 rows)

3、建议方法:

注意方法2 不适合非等值查询，

  1. postgres=# explain ( analyze,verbose,timing,costs,buffers) select * from tbl where c1=200 and c2 between 100 and 300 order by id limit 10;  
  2.                                                                   QUERY PLAN                                                                    
  3. ----------------------------------------------------------------------------------------------------------------------------------------------  
  4.  Limit  (cost=0.43..35.32 rows=10 width=20) (actual time=1371.094..1371.099 rows=10 loops=1)  
  5.    Output: id, c1, c2, c3, c4  
  6.    Buffers: shared hit=91020  
  7.    ->  Index Scan using idx_tbl_1 on public.tbl  (cost=0.43..350743.84 rows=100533 width=20) (actual time=1371.092..1371.095 rows=10 loops=1)  
  8.          Output: id, c1, c2, c3, c4  
  9.          Filter: ((tbl.c2 >= 100) AND (tbl.c2 <= 300) AND (tbl.c1 = 200))  
  10.          Rows Removed by Filter: 10000000  
  11.          Buffers: shared hit=91020  
  12.  Planning time: 0.278 ms  
  13.  Execution time: 1371.128 ms  
  14. (10 rows)

但是不用担心，我们依旧可以使用其他等值查询列，加上排序列组成复合索引，在INDEX SCAN中使用FILTER来加速。

新增如下索引，加速如下

  1. postgres=# create index idx_tbl_4 on tbl(c1,id);  
  2. CREATE INDEX  
  3.   
  4. postgres=# explain ( analyze,verbose,timing,costs,buffers) select * from tbl where c1=200 and c2 between 100 and 300 order by id limit 10;  
  5.                                                                QUERY PLAN                                                                 
  6. ----------------------------------------------------------------------------------------------------------------------------------------  
  7.  Limit  (cost=0.43..10.47 rows=10 width=20) (actual time=0.105..0.110 rows=10 loops=1)  
  8.    Output: id, c1, c2, c3, c4  
  9.    Buffers: shared hit=1 read=3  
  10.    I/O Timings: read=0.051  
  11.    ->  Index Scan using idx_tbl_4 on public.tbl  (cost=0.43..100877.50 rows=100533 width=20) (actual time=0.104..0.107 rows=10 loops=1)  
  12.          Output: id, c1, c2, c3, c4  
  13.          Index Cond: (tbl.c1 = 200)  
  14.          Filter: ((tbl.c2 >= 100) AND (tbl.c2 <= 300))  
  15.          Buffers: shared hit=1 read=3  
  16.          I/O Timings: read=0.051  
  17.  Planning time: 0.172 ms  
  18.  Execution time: 0.134 ms  
  19. (12 rows)

附Oracle

《Oracle migration to Greenplum - (含 Ora2pg)》

http://www.dba-oracle.com/t_OracleAutotrace.htm

  1. SQL> create table tbl(id int, c1 int, c2 int, c3 int, c4 int);
  2.  
  3. Table created.
  4.  
  5. SQL> insert into tbl select rownum,trunc(dbms_random.value(0, 100)),trunc(dbms_random.value(0, 100)),trunc(dbms_random.value(0, 100)),trunc(dbms_random.value(0, 100)) from dual connect by level <=10000000;
  6.  
  7. 10000000 rows created.
  8.  
  9. SQL> commit;
  10.  
  11. Commit complete.
  12.  
  13. SQL> insert into tbl select rownum+10000000, 200,200,200,200 from dual connect by level <=1000000;
  14.  
  15. 1000000 rows created.
  16.  
  17. SQL> commit;
  18.  
  19. Commit complete.
  20.  
  21. SQL> create index idx_tbl_1 on tbl(id);
  22.  
  23. Index created.
  24.  
  25. SQL> create index idx_tbl_2 on tbl(c1,c2,c3,c4);
  26.  
  27. Index created.
  28.  
  29. SQL> set linesize 512
  30. SQL> set pagesize 50000
  31.  
  32. SQL> set autotrace on;
  33.  
  34. SQL> exec DBMS_STATS.GATHER_TABLE_STATS('JIUDU','TBL'); 
  35.  
  36. PL/SQL procedure successfully completed.
  37.  
  38. SQL> select * from (select * from tbl where c1=200 and c2=200 order by id) t where rownum<10;
  39.  
  40.         ID         C1         C2         C3         C4
  41. ---------- ---------- ---------- ---------- ----------
  42.   10000001        200        200        200        200
  43.   10000002        200        200        200        200
  44.   10000003        200        200        200        200
  45.   10000004        200        200        200        200
  46.   10000005        200        200        200        200
  47.   10000006        200        200        200        200
  48.   10000007        200        200        200        200
  49.   10000008        200        200        200        200
  50.   10000009        200        200        200        200
  51.  
  52. 9 rows selected.
  53.  
  54.  
  55. Execution Plan
  56. ----------------------------------------------------------
  57. Plan hash value: 745043579
  58.  
  59. ----------------------------------------------------------------------------------------
  60. | Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  61. ----------------------------------------------------------------------------------------
  62. |   0 | SELECT STATEMENT        |      |     9 |   585 |       | 10253   (2)| 00:02:04 |
  63. |*  1 |  COUNT STOPKEY          |      |       |       |       |            |          |
  64. |   2 |   VIEW                  |      | 84875 |  5387K|       | 10253   (2)| 00:02:04 |
  65. |*  3 |    SORT ORDER BY STOPKEY|      | 84875 |  1491K|  2672K| 10253   (2)| 00:02:04 |
  66. |*  4 |     TABLE ACCESS FULL   | TBL  | 84875 |  1491K|       |  9767   (2)| 00:01:58 |
  67. ----------------------------------------------------------------------------------------
  68.  
  69. Predicate Information (identified by operation id):
  70. ---------------------------------------------------
  71.  
  72.    1 - filter(ROWNUM<10)
  73.    3 - filter(ROWNUM<10)
  74.    4 - filter("C1"=200 AND "C2"=200)
  75.  
  76.  
  77. Statistics
  78. ----------------------------------------------------------
  79.           0  recursive calls
  80.           0  db block gets
  81.       34868  consistent gets
  82.           0  physical reads
  83.           0  redo size
  84.         937  bytes sent via SQL*Net to client
  85.         500  bytes received via SQL*Net from client
  86.           2  SQL*Net roundtrips to/from client
  87.           1  sorts (memory)
  88.           0  sorts (disk)
  89.           9  rows processed
  90.  
  91. SQL> select * from (select * from tbl where c1=1 and c2=1 order by id) t where rownum<10;
  92.  
  93.         ID         C1         C2         C3         C4
  94. ---------- ---------- ---------- ---------- ----------
  95.       9697          1          1         78         39
  96.      20586          1          1         81         71
  97.      27820          1          1         33         64
  98.      44324          1          1         26         27
  99.      47079          1          1          3          5
  100.      64669          1          1         13         49
  101.      73715          1          1         20         74
  102.      80903          1          1         96         25
  103.      98368          1          1         59          9
  104.  
  105. 9 rows selected.
  106.  
  107.  
  108. Execution Plan
  109. ----------------------------------------------------------
  110. Plan hash value: 447312937
  111.  
  112. --------------------------------------------------------------------------------------------
  113. | Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  114. --------------------------------------------------------------------------------------------
  115. |   0 | SELECT STATEMENT               |           |     9 |   585 |   641   (1)| 00:00:08 |
  116. |*  1 |  COUNT STOPKEY                 |           |       |       |            |          |
  117. |   2 |   VIEW                         |           |   704 | 45760 |   641   (1)| 00:00:08 |
  118. |*  3 |    SORT ORDER BY STOPKEY       |           |   704 | 12672 |   641   (1)| 00:00:08 |
  119. |   4 |     TABLE ACCESS BY INDEX ROWID| TBL       |   704 | 12672 |   640   (0)| 00:00:08 |
  120. |*  5 |      INDEX RANGE SCAN          | IDX_TBL_2 |   704 |       |     5   (0)| 00:00:01 |
  121. --------------------------------------------------------------------------------------------
  122.  
  123. Predicate Information (identified by operation id):
  124. ---------------------------------------------------
  125.  
  126.    1 - filter(ROWNUM<10)
  127.    3 - filter(ROWNUM<10)
  128.    5 - access("C1"=1 AND "C2"=1)
  129.  
  130.  
  131. Statistics
  132. ----------------------------------------------------------
  133.           1  recursive calls
  134.           0  db block gets
  135.        1072  consistent gets
  136.          11  physical reads
  137.           0  redo size
  138.         969  bytes sent via SQL*Net to client
  139.         500  bytes received via SQL*Net from client
  140.           2  SQL*Net roundtrips to/from client
  141.           1  sorts (memory)
  142.           0  sorts (disk)
  143.           9  rows processed
  144.  
  145.  
  146. SQL> create index idx_tbl_3 on tbl(c1,c2,id);
  147. Index created.
  148.  
  149. SQL> select * from (select * from tbl where c1=200 and c2 between 100 and 300 order by id) t where rownum < 10;
  150.  
  151.         ID         C1         C2         C3         C4
  152. ---------- ---------- ---------- ---------- ----------
  153.   10000001        200        200        200        200
  154.   10000002        200        200        200        200
  155.   10000003        200        200        200        200
  156.   10000004        200        200        200        200
  157.   10000005        200        200        200        200
  158.   10000006        200        200        200        200
  159.   10000007        200        200        200        200
  160.   10000008        200        200        200        200
  161.   10000009        200        200        200        200
  162.  
  163. 9 rows selected.
  164.  
  165.  
  166. Execution Plan
  167. ----------------------------------------------------------
  168. Plan hash value: 745043579
  169.  
  170. ----------------------------------------------------------------------------------------
  171. | Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  172. ----------------------------------------------------------------------------------------
  173. |   0 | SELECT STATEMENT        |      |     9 |   585 |       | 10253   (2)| 00:02:04 |
  174. |*  1 |  COUNT STOPKEY          |      |       |       |       |            |          |
  175. |   2 |   VIEW                  |      | 84875 |  5387K|       | 10253   (2)| 00:02:04 |
  176. |*  3 |    SORT ORDER BY STOPKEY|      | 84875 |  1491K|  2672K| 10253   (2)| 00:02:04 |
  177. |*  4 |     TABLE ACCESS FULL   | TBL  | 84875 |  1491K|       |  9767   (2)| 00:01:58 |
  178. ----------------------------------------------------------------------------------------
  179.  
  180. Predicate Information (identified by operation id):
  181. ---------------------------------------------------
  182.  
  183.    1 - filter(ROWNUM<10)
  184.    3 - filter(ROWNUM<10)
  185.    4 - filter("C1"=200 AND "C2">=100 AND "C2"<=300)
  186.  
  187.  
  188. Statistics
  189. ----------------------------------------------------------
  190.           1  recursive calls
  191.           0  db block gets
  192.       34868  consistent gets
  193.           0  physical reads
  194.           0  redo size
  195.         937  bytes sent via SQL*Net to client
  196.         500  bytes received via SQL*Net from client
  197.           2  SQL*Net roundtrips to/from client
  198.           1  sorts (memory)
  199.           0  sorts (disk)
  200.           9  rows processed
  201.  
  202. SQL> select * from (select * from tbl where c1=200 and c2 =200 order by id) t where rownum < 10;
  203.  
  204.         ID         C1         C2         C3         C4
  205. ---------- ---------- ---------- ---------- ----------
  206.   10000001        200        200        200        200
  207.   10000002        200        200        200        200
  208.   10000003        200        200        200        200
  209.   10000004        200        200        200        200
  210.   10000005        200        200        200        200
  211.   10000006        200        200        200        200
  212.   10000007        200        200        200        200
  213.   10000008        200        200        200        200
  214.   10000009        200        200        200        200
  215.  
  216. 9 rows selected.
  217.  
  218.  
  219. Execution Plan
  220. ----------------------------------------------------------
  221. Plan hash value: 1825274432
  222.  
  223. -------------------------------------------------------------------------------------------
  224. | Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  225. -------------------------------------------------------------------------------------------
  226. |   0 | SELECT STATEMENT              |           |     9 |   585 |    12   (0)| 00:00:01 |
  227. |*  1 |  COUNT STOPKEY                |           |       |       |            |          |
  228. |   2 |   VIEW                        |           |    10 |   650 |    12   (0)| 00:00:01 |
  229. |   3 |    TABLE ACCESS BY INDEX ROWID| TBL       |    10 |   180 |    12   (0)| 00:00:01 |
  230. |*  4 |     INDEX RANGE SCAN          | IDX_TBL_3 |       |       |     3   (0)| 00:00:01 |
  231. -------------------------------------------------------------------------------------------
  232.  
  233. Predicate Information (identified by operation id):
  234. ---------------------------------------------------
  235.  
  236.    1 - filter(ROWNUM<10)
  237.    4 - access("C1"=200 AND "C2"=200)
  238.  
  239.  
  240. Statistics
  241. ----------------------------------------------------------
  242.           1  recursive calls
  243.           0  db block gets
  244.           6  consistent gets
  245.           2  physical reads
  246.           0  redo size
  247.         937  bytes sent via SQL*Net to client
  248.         500  bytes received via SQL*Net from client
  249.           2  SQL*Net roundtrips to/from client
  250.           0  sorts (memory)
  251.           0  sorts (disk)
  252.           9  rows processed
  253.

同时也发现一个问题，Oracle可能无法使用index filter来优化，例如将index2,index3删除后，留下ID索引，Oracle无法走索引，而PG可以。

  1. SQL> select * from (select * from tbl where c1=1 and c2 =1 order by id) t where rownum < 10;
  2.  
  3.         ID         C1         C2         C3         C4
  4. ---------- ---------- ---------- ---------- ----------
  5.       9697          1          1         78         39
  6.      20586          1          1         81         71
  7.      27820          1          1         33         64
  8.      44324          1          1         26         27
  9.      47079          1          1          3          5
  10.      64669          1          1         13         49
  11.      73715          1          1         20         74
  12.      80903          1          1         96         25
  13.      98368          1          1         59          9
  14.  
  15. 9 rows selected.
  16.  
  17.  
  18. Execution Plan
  19. ----------------------------------------------------------
  20. Plan hash value: 745043579
  21.  
  22. --------------------------------------------------------------------------------
  23. | Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  24. --------------------------------------------------------------------------------
  25. |   0 | SELECT STATEMENT        |      |     9 |   585 |  9766   (2)| 00:01:58 |
  26. |*  1 |  COUNT STOPKEY          |      |       |       |            |          |
  27. |   2 |   VIEW                  |      |   704 | 45760 |  9766   (2)| 00:01:58 |
  28. |*  3 |    SORT ORDER BY STOPKEY|      |   704 | 12672 |  9766   (2)| 00:01:58 |
  29. |*  4 |     TABLE ACCESS FULL   | TBL  |   704 | 12672 |  9765   (2)| 00:01:58 |
  30. --------------------------------------------------------------------------------
  31.  
  32. Predicate Information (identified by operation id):
  33. ---------------------------------------------------
  34.  
  35.    1 - filter(ROWNUM<10)
  36.    3 - filter(ROWNUM<10)
  37.    4 - filter("C1"=1 AND "C2"=1)
  38.  
  39.  
  40. Statistics
  41. ----------------------------------------------------------
  42.         186  recursive calls
  43.           0  db block gets
  44.       34893  consistent gets
  45.           0  physical reads
  46.           0  redo size
  47.         969  bytes sent via SQL*Net to client
  48.         500  bytes received via SQL*Net from client
  49.           2  SQL*Net roundtrips to/from client
  50.           7  sorts (memory)
  51.           0  sorts (disk)
  52.           9  rows processed
  53.   
  54.  
  55. PG
  56.  
  57.  
  58. postgres=# explain analyze select * from tbl where c1=1 and c2 =1 order by id limit 10;
  59.                                                           QUERY PLAN                                                           
  60. -------------------------------------------------------------------------------------------------------------------------------
  61.  Limit  (cost=0.43..3703.11 rows=10 width=20) (actual time=7.199..23.926 rows=10 loops=1)
  62.    ->  Index Scan using idx_tbl_1 on tbl  (cost=0.43..323243.84 rows=873 width=20) (actual time=7.198..23.921 rows=10 loops=1)
  63.          Filter: ((c1 = 1) AND (c2 = 1))
  64.          Rows Removed by Filter: 142814
  65.  Planning time: 0.119 ms
  66.  Execution time: 23.950 ms
  67. (6 rows)

小结

当SQL查询中包括排序，以及其他字段的过滤条件，并使用LIMIT快速返回少量数据时，如果满足条件的数据分布在排序键的末端，那么优化器给出的执行计划可能是不好的，导致通过排序索引扫描更多的数据后才能命中需要的记录。

然而，数据库目前使用的评估走排序键时，LIMIT需要扫描多少条记录，使用了数据均匀分布的假设，所以在数据（满足条件的数据与排序键本身的相关性不均匀）分布不均匀时，导致成本估算不准(oracle干脆走全表扫描)。

建议优化方法：

增加索引，创建等值查询条件列(s)加排序列(s)组成的复合索引，降低扫描量。

例子

  1. select * from tbl where c1=200 and c2 between 100 and 300 order by id limit 10;  
  2.   
  3. 增加索引  
  4.   
  5. (c1,id)  -- 索引扫描, filter c2  
  6.   
  7. 已有  
  8. (c1,c2)  -- 索引扫描, sort id  
  9. (id)     -- 索引扫描, filter c1,c2  
  1. select * from tbl where c1=200 and c2 =200 order by id limit 10;  
  2.   
  3. 增加索引  
  4.   
  5. (c1,c2,id)  -- 索引扫描  
  6.   
  7. 已有  
  8. (c1,c2)  -- 索引扫描, sort id  
  9. (id)     -- 索引扫描, filter c1,c2

参考

《PostgreSQL 10 黑科技 - 自定义统计信息》

本文内容由网友自发贡献，转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/135185
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号