表是在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模式下能获得更好的兼容性和性能表现, 或如上创建函数索引。