PostgreSQL Oracle 兼容性 - Oracle反转索引 迁移到PostgreSQL可用函数索引或哈希索引

背景

Oracle为了解决索引页热点的问题，推出了反转索引。

应用场景如下，当用户插入的数据是有序数据时，并且遇到高并发的插入请求，可能造成索引页的写竞争，从而增加了等待时间。

反转索引是指索引中存储的是反转值，人为的将有序的数据转换为无序的数据，从而减少索引页的写竞争。但是反转索引有个问题，如果要查询一个范围的值，显然无法使用反转索引。同时反转索引的prefix匹配也和普通索引的prefix匹配相反，即匹配的是倒过来的prefix。

了解了反转索引的用意之后，当需要将Oracle迁移到PostgreSQL时，就非常好处理了，我们直接使用函数索引就可以达到同样的目的，如果用户不需要使用prefix检索，实际上用PostgreSQL的hash index也可以达到同样的目的，只是需要注意在流复制场景中的应用。

《请勿滥用unlogged table & hash index》

下面我们测试一下这种应用场景，反转索引和普通索引的性能差异。

高并发的插入顺序数据，顺序数据由时间产生。

在这种应用场景中，反转和哈希索引比一般索引性能要高一些。

pipeline=# select 100000*extract(epoch from now());  
    ?column?       
-----------------  
 144540795728239  
(1 row)  
pipeline=# select 100000*extract(epoch from now());  
    ?column?       
-----------------  
 144540795813935  
(1 row)  
pipeline=# select 100000*extract(epoch from now());  
    ?column?       
-----------------  
 144540795879135  
(1 row)  

测试脚本

vi test.sql  
insert into test (id) values (100000*extract(epoch from now()));  

普通索引

pipeline=# create table test(id text , info text);  
CREATE TABLE  
pipeline=# create index idx_test on test using btree(id);  
CREATE INDEX  
  
pgbench -M prepared -n -r -f ./test.sql -P 1 -c 64 -j 64 -p 1950 -T 32  
progress: 1.0 s, 122544.9 tps, lat 0.512 ms stddev 0.455  
progress: 2.0 s, 124966.5 tps, lat 0.511 ms stddev 0.445  
progress: 3.0 s, 123671.4 tps, lat 0.516 ms stddev 0.460  
progress: 4.0 s, 125296.1 tps, lat 0.509 ms stddev 0.451  
progress: 5.0 s, 125045.0 tps, lat 0.510 ms stddev 0.449  
progress: 6.0 s, 125259.7 tps, lat 0.509 ms stddev 0.446  

反转函数索引

pipeline=# create table test(id text , info text);  
CREATE TABLE  
pipeline=# create index idx_test on test using btree(reverse(id));  
CREATE INDEX  
  
pgbench -M prepared -n -r -f ./test.sql -P 1 -c 64 -j 64 -p 1950 -T 32  
progress: 1.0 s, 147795.0 tps, lat 0.424 ms stddev 0.478  
progress: 2.0 s, 150917.0 tps, lat 0.423 ms stddev 0.471  
progress: 3.0 s, 148530.5 tps, lat 0.429 ms stddev 0.485  
progress: 4.0 s, 151123.5 tps, lat 0.422 ms stddev 0.473  
progress: 5.0 s, 150637.0 tps, lat 0.423 ms stddev 0.472  
progress: 6.0 s, 150277.0 tps, lat 0.424 ms stddev 0.472  

哈希索引

pipeline=# create table test(id text , info text);  
CREATE TABLE  
pipeline=# create index idx_test on test using hash(id);  
CREATE INDEX  
  
pgbench -M prepared -n -r -f ./test.sql -P 1 -c 64 -j 64 -p 1950 -T 32  
progress: 1.0 s, 144940.0 tps, lat 0.433 ms stddev 0.494  
progress: 2.0 s, 148202.8 tps, lat 0.430 ms stddev 0.490  
progress: 3.0 s, 148085.2 tps, lat 0.431 ms stddev 0.485  
progress: 4.0 s, 147500.5 tps, lat 0.432 ms stddev 0.488  
progress: 5.0 s, 147387.9 tps, lat 0.433 ms stddev 0.491  
progress: 6.0 s, 147481.0 tps, lat 0.432 ms stddev 0.486  

函数索引prefix查询支持

pipeline=# explain select * from test where reverse(id) ~ '^abc';  
                                     QUERY PLAN                                       
------------------------------------------------------------------------------------  
 Bitmap Heap Scan on test  (cost=217.76..9607.89 rows=8325 width=48)  
   Filter: (reverse(id) ~ '^abc'::text)  
   ->  Bitmap Index Scan on idx_test  (cost=0.00..215.68 rows=8325 width=0)  
         Index Cond: ((reverse(id) >= 'abc'::text) AND (reverse(id) < 'abd'::text))  
(4 rows)  

digoal’s 大量PostgreSQL文章入口