Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL != , <> 索引

6 minute read

背景

在数据库中不等于能不能走索引呢？理论上是不行的，但是有方法可以让不等于也走索引(虽然走索引也不一定好)。

比如使用外连接实现(需要表有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 loops=1)  
         Output: t1.id, t1.c1  
         Buffers: shared hit=4425  
   ->  Hash  (cost=351.32..351.32 rows=98 width=36) (actual time=0.211..0.211 rows=81 loops=1)  
         Output: t2.id, t2.*  
         Buckets: 1024  Batches: 1  Memory Usage: 6kB  
         Buffers: shared hit=84  
         ->  Bitmap Heap Scan on public.tbl_pk t2  (cost=5.18..351.32 rows=98 width=36) (actual time=0.044..0.185 rows=81 loops=1)  
               Output: t2.id, t2.*  
               Recheck Cond: (t2.c1 = 1)  
               Heap Blocks: exact=81  
               Buffers: shared hit=84  
               ->  Bitmap Index Scan on idx_tbl_pk  (cost=0.00..5.16 rows=98 width=0) (actual time=0.025..0.025 rows=81 loops=1)  
                     Index Cond: (t2.c1 = 1)  
                     Buffers: shared hit=3  
 Planning time: 0.289 ms  
 Execution time: 461.386 ms  
(23 rows)  
  
postgres=# set enable_seqscan=off;  
SET  
  
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                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------  
 Merge Left Join  (cost=354.99..33264.46 rows=5000 width=8) (actual time=0.224..482.241 rows=999919 loops=1)  
   Output: t1.id, t1.c1  
   Merge Cond: (t1.id = t2.id)  
   Filter: (t2.* IS NULL)  
   Rows Removed by Filter: 81  
   Buffers: shared hit=7244  
   ->  Index Scan using tbl_pk_pkey on public.tbl_pk t1  (cost=0.42..30408.42 rows=1000000 width=8) (actual time=0.008..215.816 rows=1000000 loops=1)  
         Output: t1.id, t1.c1  
         Buffers: shared hit=7160  
   ->  Sort  (cost=354.57..354.81 rows=98 width=36) (actual time=0.211..0.233 rows=81 loops=1)  
         Output: t2.id, t2.*  
         Sort Key: t2.id  
         Sort Method: quicksort  Memory: 31kB  
         Buffers: shared hit=84  
         ->  Bitmap Heap Scan on public.tbl_pk t2  (cost=5.18..351.32 rows=98 width=36) (actual time=0.046..0.183 rows=81 loops=1)  
               Output: t2.id, t2.*  
               Recheck Cond: (t2.c1 = 1)  
               Heap Blocks: exact=81  
               Buffers: shared hit=84  
               ->  Bitmap Index Scan on idx_tbl_pk  (cost=0.00..5.16 rows=98 width=0) (actual time=0.026..0.026 rows=81 loops=1)  
                     Index Cond: (t2.c1 = 1)  
                     Buffers: shared hit=3  
 Planning time: 0.275 ms  
 Execution time: 548.991 ms  
(24 rows)  
  
postgres=# set enable_sort=off;  
SET  
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.97..34511.95 rows=5000 width=8) (actual time=0.235..494.068 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=7244  
   ->  Index Scan using tbl_pk_pkey on public.tbl_pk t1  (cost=0.42..30408.42 rows=1000000 width=8) (actual time=0.008..223.681 rows=1000000 loops=1)  
         Output: t1.id, t1.c1  
         Buffers: shared hit=7160  
   ->  Hash  (cost=351.32..351.32 rows=98 width=36) (actual time=0.218..0.218 rows=81 loops=1)  
         Output: t2.id, t2.*  
         Buckets: 1024  Batches: 1  Memory Usage: 6kB  
         Buffers: shared hit=84  
         ->  Bitmap Heap Scan on public.tbl_pk t2  (cost=5.18..351.32 rows=98 width=36) (actual time=0.051..0.201 rows=81 loops=1)  
               Output: t2.id, t2.*  
               Recheck Cond: (t2.c1 = 1)  
               Heap Blocks: exact=81  
               Buffers: shared hit=84  
               ->  Bitmap Index Scan on idx_tbl_pk  (cost=0.00..5.16 rows=98 width=0) (actual time=0.031..0.031 rows=81 loops=1)  
                     Index Cond: (t2.c1 = 1)  
                     Buffers: shared hit=3  
 Planning time: 0.274 ms  
 Execution time: 560.676 ms  
(23 rows)  
  
postgres=# set enable_hashjoin=off;  
SET  
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                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------  
 Merge Left Join  (cost=0.85..65818.07 rows=5000 width=8) (actual time=1.080..664.991 rows=999919 loops=1)  
   Output: t1.id, t1.c1  
   Merge Cond: (t1.id = t2.id)  
   Filter: (t2.* IS NULL)  
   Rows Removed by Filter: 81  
   Buffers: shared hit=14320  
   ->  Index Scan using tbl_pk_pkey on public.tbl_pk t1  (cost=0.42..30408.42 rows=1000000 width=8) (actual time=0.007..208.319 rows=1000000 loops=1)  
         Output: t1.id, t1.c1  
         Buffers: shared hit=7160  
   ->  Index Scan using tbl_pk_pkey on public.tbl_pk t2  (cost=0.42..32908.43 rows=98 width=36) (actual time=1.067..193.630 rows=81 loops=1)  
         Output: t2.id, t2.*  
         Filter: (t2.c1 = 1)  
         Rows Removed by Filter: 999919  
         Buffers: shared hit=7160  
 Planning time: 0.261 ms  
 Execution time: 732.070 ms  
(16 rows)

例子2, 没有PK

没有PK时，可以使用行号进行关联。

postgres=# create index tbl_pk_idx2 on tbl_pk (ctid);  
CREATE INDEX  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_pk t1 left join tbl_pk t2 on (t1.ctid=t2.ctid and t2.c1=1) where t2.* is null;  
                                                                      QUERY PLAN                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------  
 Merge Left Join  (cost=0.85..92362.07 rows=5000 width=8) (actual time=1.232..720.467 rows=999919 loops=1)  
   Output: t1.id, t1.c1  
   Merge Cond: (t1.ctid = t2.ctid)  
   Filter: (t2.* IS NULL)  
   Rows Removed by Filter: 81  
   Buffers: shared hit=11585 read=2735  
   ->  Index Scan using tbl_pk_idx2 on public.tbl_pk t1  (cost=0.42..43680.43 rows=1000000 width=14) (actual time=0.041..268.576 rows=1000000 loops=1)  
         Output: t1.id, t1.c1, t1.ctid  
         Buffers: shared hit=7157 read=3  
   ->  Index Scan using tbl_pk_idx2 on public.tbl_pk t2  (cost=0.42..46180.43 rows=98 width=38) (actual time=1.186..213.849 rows=81 loops=1)  
         Output: t2.ctid, t2.*  
         Filter: (t2.c1 = 1)  
         Rows Removed by Filter: 999919  
         Buffers: shared hit=4428 read=2732  
 Planning time: 0.336 ms  
 Execution time: 787.713 ms  
(16 rows)

例子3, 数组查询中的不包含

postgres=# create table tbl_arr(id int[], c1 text);  
CREATE TABLE  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# insert into tbl_arr select array_agg(100000*random()) from generate_series(1,100);  
INSERT 0 1  
postgres=# create index idx_tbl_arr_1 on tbl_arr using gin (id);  
CREATE INDEX  
postgres=# create index idx_tbl_arr_2 on tbl_arr (ctid);  
CREATE INDEX  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_arr where not (id @> array[1,2]);  
                                                QUERY PLAN                                                   
-----------------------------------------------------------------------------------------------------------  
 Seq Scan on public.tbl_arr  (cost=0.00..1.12 rows=10 width=64) (actual time=0.017..0.029 rows=10 loops=1)  
   Output: id, c1  
   Filter: (NOT (tbl_arr.id @> '{1,2}'::integer[]))  
   Buffers: shared hit=1  
 Planning time: 0.086 ms  
 Execution time: 0.051 ms  
(6 rows)  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_arr t1 left join tbl_arr t2 on (t1.ctid=t2.ctid and t2.id @> array[1,2]) where t2.* is null;  
                                                                QUERY PLAN                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------  
 Merge Left Join  (cost=0.27..24.63 rows=1 width=64) (actual time=0.133..0.137 rows=10 loops=1)  
   Output: t1.id, t1.c1  
   Merge Cond: (t1.ctid = t2.ctid)  
   Filter: (t2.* IS NULL)  
   Buffers: shared hit=3 read=1  
   ->  Index Scan using idx_tbl_arr_2 on public.tbl_arr t1  (cost=0.14..12.29 rows=10 width=70) (actual time=0.108..0.110 rows=10 loops=1)  
         Output: t1.id, t1.c1, t1.ctid  
         Buffers: shared hit=1 read=1  
   ->  Index Scan using idx_tbl_arr_2 on public.tbl_arr t2  (cost=0.14..12.31 rows=1 width=94) (actual time=0.022..0.022 rows=0 loops=1)  
         Output: t2.ctid, t2.*  
         Filter: (t2.id @> '{1,2}'::integer[])  
         Rows Removed by Filter: 10  
         Buffers: shared hit=2  
 Planning time: 0.193 ms  
 Execution time: 0.173 ms  
(15 rows)  

postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.* from tbl_arr t1 where not exists (select 1 from tbl_arr t2 where t1.ctid=t2.ctid and t2.id @> array[1,2]);
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Merge Anti Join  (cost=0.27..24.63 rows=9 width=64) (actual time=0.023..0.026 rows=10 loops=1)
   Output: t1.id, t1.c1
   Merge Cond: (t1.ctid = t2.ctid)
   Buffers: shared hit=4
   ->  Index Scan using idx_tbl_arr_2 on public.tbl_arr t1  (cost=0.14..12.29 rows=10 width=70) (actual time=0.004..0.005 rows=10 loops=1)
         Output: t1.id, t1.c1, t1.ctid
         Buffers: shared hit=2
   ->  Index Scan using idx_tbl_arr_2 on public.tbl_arr t2  (cost=0.14..12.31 rows=1 width=6) (actual time=0.017..0.017 rows=0 loops=1)
         Output: t2.ctid
         Filter: (t2.id @> '{1,2}'::integer[])
         Rows Removed by Filter: 10
         Buffers: shared hit=2
 Planning time: 0.103 ms
 Execution time: 0.047 ms
(14 rows)

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...