标签
PostgreSQL , 不等于 , 索引 , 外连接
背景
在数据库中不等于能不能走索引呢?理论上是不行的,但是有方法可以让不等于也走索引(虽然走索引也不一定好)。
比如使用外连接实现(需要表有PK才行,没有PK可以使用行号代替),或者使用not exists,或者使用partial index(不支持变量)。
对于返回结果集很大的场景,建议使用游标分页返回,此时可能用全表扫描更适合。
例子1, 有PK
create table tbl_pk(id int primary key, c1 int);
create index idx_tbl_pk on tbl_pk(c1);
insert into tbl_pk select generate_series(1,1000000), random()*10000;
原始方法,不能走索引
select * from tbl_pk where c1 <> 1;
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_pk where c1 <> 1;
QUERY PLAN
Seq Scan on public.tbl_pk (cost=0.00..16925.00 rows=999902 width=8) (actual time=0.020..182.603 rows=999919 loops=1)
Output: id, c1
Filter: (tbl_pk.c1 <> 1)
Rows Removed by Filter: 81
Buffers: shared hit=4425
Planning time: 0.486 ms
Execution time: 249.335 ms
(7 rows)
让他走索引的写法
postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_pk t1 left join tbl_pk t2 on (t1.id=t2.id and t2.c1=1) where t2.* is null;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=352.55..18528.53 rows=5000 width=8) (actual time=0.229..395.158 rows=999919 loops=1)
Output: t1.id, t1.c1
Hash Cond: (t1.id = t2.id)
Filter: (t2.* IS NULL)
Rows Removed by Filter: 81
Buffers: shared hit=4509
-> Seq Scan on public.tbl_pk t1 (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.009..128.644 rows=1000000