ANBOB™

表是在Oracle模式下创建的，包含NUMBER数据类型的列，在PostGreSQL模式下查询这些表时，Oracle模式下的NUMBER类型与PG模式下的数值类型不完全匹配, 数据类型隐式转换，导致无法使用索引范围扫.

演示

Highgo 的oracle 模式

[hg@dev]$ psql highgo system -p 1521
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.


highgo=# \l
                                        List of databases
          Name           | Owner | Encoding |   Collate   |    Ctype    |    Access privileges
-------------------------+-------+----------+-------------+-------------+-------------------------
 anbob                   | hg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 highgo                  | hg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
...
(9 rows)

highgo=# \c anbob
IvorySQL-pro-3.2.0
You are now connected to database "anbob" as user "system".
                              ^
anbob=# create table tnum(id number,id1 int,id2 double precision,name varchar(100));
CREATE TABLE

anbob=# insert into tnum select x,x,x,'anbob'||x from generate_series(1,10000) as x;
INSERT 0 10000

anbob=# \d tnum
                    Table "public.tnum"
 Column |       Type       | Collation | Nullable | Default
--------+------------------+-----------+----------+---------
 id     | number           |           |          |
 id1    | integer          |           |          |
 id2    | double precision |           |          |
 name   | varchar2(100)    |           |          |

anbob=# create index tnum_num on tnum(id);
CREATE INDEX
anbob=# create index tnum_int on tnum(id1);
CREATE INDEX
anbob=# create index tnum_doub on tnum(id2);
CREATE INDEX
anbob=# vacuum analyze tnum;
VACUUM
anbob=# \d+ tnum
                                               Table "public.tnum"
 Column |       Type       | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | number           |           |          |         | main     |             |              |
 id1    | integer          |           |          |         | plain    |             |              |
 id2    | double precision |           |          |         | plain    |             |              |
 name   | varchar2(100)    |           |          |         | extended |             |              |
Indexes:
    "tnum_doub" btree (id2)
    "tnum_int" btree (id1)
    "tnum_num" btree (id)
Access method: heap

anbob=# explain analyze select * from tnum where id=1;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Index Scan using tnum_num on tnum  (cost=0.29..3.30 rows=1 width=26) (actual time=0.082..0.083 rows=1 loops=1)
   Index Cond: (id = '1'::number)
 Planning Time: 0.340 ms
 Execution Time: 0.123 ms
(4 rows)

anbob=# explain analyze select * from tnum where id1=1;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Index Scan using tnum_int on tnum  (cost=0.29..3.30 rows=1 width=26) (actual time=0.111..0.115 rows=1 loops=1)
   Index Cond: (id1 = 1)
 Planning Time: 0.141 ms
 Execution Time: 0.148 ms
(4 rows)

anbob=# explain analyze select * from tnum where id2=1;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Scan using tnum_doub on tnum  (cost=0.29..3.30 rows=1 width=26) (actual time=0.070..0.072 rows=1 loops=1)
   Index Cond: (id2 = '1'::double precision)
 Planning Time: 0.179 ms
 Execution Time: 0.095 ms
(4 rows)

注： 在oracle 模式中可以使用索引。

 Highgo 的postgresql模式

[hg@dev data]$ psql  -p 5866
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.

highgo=# \l
                                        List of databases
          Name           | Owner | Encoding |   Collate   |    Ctype    |    Access privileges
-------------------------+-------+----------+-------------+-------------+-------------------------
 anbob                   | hg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 highgo                  | hg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
...
(9 rows)

highgo=# \c anbob
IvorySQL-pro-3.2.0
You are now connected to database "anbob" as user "hg".
anbob=# \d+ tnum
                                               Table "public.tnum"
 Column |       Type       | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | number           |           |          |         | main     |             |              |
 id1    | integer          |           |          |         | plain    |             |              |
 id2    | double precision |           |          |         | plain    |             |              |
 name   | varchar2(100)    |           |          |         | extended |             |              |
Indexes:
    "tnum_doub" btree (id2)
    "tnum_int" btree (id1)
    "tnum_num" btree (id)
Access method: heap

anbob=# explain analyze select * from tnum where id=1;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on tnum  (cost=0.00..234.00 rows=50 width=26) (actual time=0.025..3.695 rows=1 loops=1)
   Filter: ((id)::double precision = '1'::double precision)       <<<<<<<<     ！！！！   number type  never use indexes
   Rows Removed by Filter: 9999
 Planning Time: 0.338 ms
 Execution Time: 3.775 ms
(5 rows)

anbob=# explain analyze select * from tnum where id1=1;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Index Scan using tnum_int on tnum  (cost=0.29..3.30 rows=1 width=26) (actual time=0.034..0.038 rows=1 loops=1)
   Index Cond: (id1 = 1)
 Planning Time: 0.136 ms
 Execution Time: 0.071 ms
(4 rows)

anbob=# explain analyze select * from tnum where id2=1;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Scan using tnum_doub on tnum  (cost=0.29..3.30 rows=1 width=26) (actual time=0.012..0.013 rows=1 loops=1)
   Index Cond: (id2 = '1'::double precision)
 Planning Time: 0.165 ms
 Execution Time: 0.036 ms
(4 rows)

anbob=#

解决方案

1，创建函数索引

anbob=# create index idx_tnum_id_cast on tnum ((id::int));
CREATE INDEX
anbob=# explain analyze select * from tnum where id=1;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on tnum  (cost=0.00..234.00 rows=50 width=26) (actual time=0.014..3.431 rows=1 loops=1)
   Filter: ((id)::double precision = '1'::double precision)
   Rows Removed by Filter: 9999
 Planning Time: 0.400 ms
 Execution Time: 3.482 ms
(5 rows)

anbob=# explain analyze select * from tnum where id::int=1;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tnum  (cost=2.17..48.14 rows=50 width=26) (actual time=0.069..0.071 rows=1 loops=1)
   Recheck Cond: ((id)::integer = 1)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_tnum_id_cast  (cost=0.00..2.16 rows=50 width=0) (actual time=0.065..0.065 rows=1 loops=1)
         Index Cond: ((id)::integer = 1)
 Planning Time: 0.113 ms
 Execution Time: 0.125 ms
(7 rows)

anbob=# vacuum analyze tnum;
VACUUM
anbob=# explain analyze select * from tnum where id::int=1;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tnum_id_cast on tnum  (cost=0.29..3.30 rows=1 width=26) (actual time=0.008..0.010 rows=1 loops=1)
   Index Cond: ((id)::integer = 1)
 Planning Time: 0.223 ms
 Execution Time: 0.058 ms
(4 rows)

总结：

尽量在同一个模式下创建和查询表,避免跨模式操作导致的数据类型问题,当表中使用了Oracle兼容的NUMBER数据类型时，强烈建议统一使用Oracle模式进行数据库连接和操作,在PG模式下查询包含NUMBER类型列的表时，会发生隐式类型转换,这种转换会导致查询优化器无法有效利用列上的索引，特别是影响范围扫描等高效查询方式,对于不需要Oracle特殊功能的场景，可考虑使用PostgreSQL原生数据类型如INT、BIGINT或NUMERIC替代NUMBER类型,这些类型在PG模式下能获得更好的兼容性和性能表现, 或如上创建函数索引。

打赏

微信扫一扫，打赏作者吧～