空值的排序规则与性能

一、KingbaseES 对于空值的默认排序规则

KingbaseES 对于 null 值的排序默认是最大的，也就是比任何值都大。具体见以下例子：

test=# create table test(id integer,name text);
test=# insert into test values(1,'a');
test=# insert into test values(2,null);
 
test=# select * from test order by name;
 id | name
----+------
  1 | a
  2 |
(2 rows)
 
test=# select * from test order by name desc;
 id | name
----+------
  2 |
  1 | a

二、空值的排序规则对于性能的影响

请看以下例子：

create table t1(id integer,name text);
insert into t1 select generate_series(10,10000000),generate_series(10,10000000);
create index ind_t1_name on t1(name);

1、默认null 规则，都可以使用索引排序

可以看到，不管是升序还是降序，都可以使用索引

test=# explain analyze select * from t1 order by name limit 1;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.47 rows=1 width=11) (actual time=0.054..0.055 rows=1 loops=1)
   ->  Index Scan using ind_t1_name on t1  (cost=0.43..369912.18 rows=10000002 width=11) (actual time=0.053..0.054 rows=1 loops=1)
 Planning Time: 0.110 ms
 Execution Time: 0.070 ms
(4 rows)
 
test=# explain analyze select * from t1 order by name desc limit 1;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.47 rows=1 width=11) (actual time=0.040..0.040 rows=1 loops=1)
   ->  Index Scan Backward using ind_t1_name on t1  (cost=0.43..369912.18 rows=10000002 width=11) (actual time=0.039..0.039 rows=1 loops=1)
 Planning Time: 0.108 ms
 Execution Time: 0.056 ms
(4 rows)

2、指定null 规则

由于null 是最大的，降序排序时，如果指定 null first ，则可以正常使用索引。

test=# explain analyze select * from t1 order by name desc nulls first limit 1;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.47 rows=1 width=11) (actual time=0.019..0.020 rows=1 loops=1)
   ->  Index Scan Backward using ind_t1_name on t1  (cost=0.43..369912.18 rows=10000002 width=11) (actual time=0.018..0.018 rows=1 loops=1)
 Planning Time: 0.162 ms
 Execution Time: 0.034 ms
(4 rows)
 
test=# explain analyze select * from t1 order by name desc nulls last limit 1;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=204347.03..204347.03 rows=1 width=11) (actual time=2250.512..2250.513 rows=1 loops=1)
   ->  Sort  (cost=204347.03..229347.04 rows=10000002 width=11) (actual time=2250.511..2250.512 rows=1 loops=1)
         Sort Key: name DESC NULLS LAST
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on t1  (cost=0.00..154347.02 rows=10000002 width=11) (actual time=0.009..705.418 rows=9999991 loops=1)
 Planning Time: 0.067 ms
 Execution Time: 2250.533 ms
(7 rows)

如果使用升序排序，则指定nulls last 时，可以使用索引

test=# explain analyze select * from t1 order by name asc nulls first limit 1;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=204347.03..204347.03 rows=1 width=11) (actual time=2218.089..2218.090 rows=1 loops=1)
   ->  Sort  (cost=204347.03..229347.04 rows=10000002 width=11) (actual time=2218.088..2218.089 rows=1 loops=1)
         Sort Key: name NULLS FIRST
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on t1  (cost=0.00..154347.02 rows=10000002 width=11) (actual time=0.009..706.852 rows=9999991 loops=1)
 Planning Time: 0.068 ms
 Execution Time: 2218.113 ms
(7 rows)
 
test=# explain analyze select * from t1 order by name asc nulls last limit 1;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.47 rows=1 width=11) (actual time=0.020..0.020 rows=1 loops=1)
   ->  Index Scan using ind_t1_name on t1  (cost=0.43..369912.18 rows=10000002 width=11) (actual time=0.019..0.019 rows=1 loops=1)
 Planning Time: 0.076 ms
 Execution Time: 0.035 ms
(4 rows)

哪种情况下能使用索引，还与创建索引的索引是升序还是降序有关。

三、创建索引时可以指定nulls first/last 选项

默认降序索引时，null 是first 的。因此，如果建索引时没有指定 nulls last ，则SQL 不能使用索引。

test=# create index ind_t1_name on t1(name desc nulls last);
CREATE INDEX
test=#  explain analyze select * from t1 order by name desc nulls last limit 10;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.80 rows=10 width=11) (actual time=0.014..0.017 rows=10 loops=1)
   ->  Index Scan using ind_t1_name on t1  (cost=0.43..369161.56 rows=9999992 width=11) (actual time=0.013..0.016 rows=10 loops=1)
 Planning Time: 0.161 ms
 Execution Time: 0.028 ms
(4 rows)