不等于 != , <>-- 1 有 pk 的情况create table it201010a ( id int not null primary key, val int not null);insert into it201010aselect generate_series(1,1e5) , random() * 10000;-- 建立 indexcreate index on it201010a(val);commit;analyze it201010a;explain (analyze,verbose,timing,costs,buffers)select * from it201010a where val <> 100;+-------------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+-------------------------------------------------------------------------------------------------------------------+| Seq Scan on aki.it201010a (cost=0.00..1693.00 rows=99990 width=8) (actual time=0.040..18.171 rows=99995 loops=1) || Output: id, val || Filter: (it201010a.val <> 100) || Rows Removed by Filter: 5 || Buffers: shared hit=443 || Planning Time: 0.123 ms || Execution Time: 31.306 ms |+-------------------------------------------------------------------------------------------------------------------+-- 可以观察到,当过滤条件是不等于(<>)时, 不使用 indexexplain (analyze,timing,costs)select t1.* from it201010a t1 left join it201010a t2 on (t1.id = t2.id and t2.val = 100) where t2.* is null;+---------------------------------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+---------------------------------------------------------------------------------------------------------------------------------------+| Hash Left Join (cost=40.24..1745.75 rows=500 width=8) (actual time=0.087..38.241 rows=99995 loops=1) || Hash Cond: (t1.id = t2.id) || Filter: (t2.* IS NULL) || Rows Removed by Filter: 5 || -> Seq Scan on it201010a t1 (cost=0.00..1443.00 rows=100000 width=8) (actual time=0.012..12.280 rows=100000 loops=1) || -> Hash (cost=40.11..40.11 rows=10 width=36) (actual time=0.060..0.061 rows=5 loops=1) || Buckets: 1024 Batches: 1 Memory Usage: 9kB || -> Bitmap Heap Scan on it201010a t2 (cost=4.50..40.11 rows=10 width=36) (actual time=0.044..0.054 rows=5 loops=1) || Recheck Cond: (val = 100) || Heap Blocks: exact=5 || -> Bitmap Index Scan on it201010a_val_idx (cost=0.00..4.49 rows=10 width=0) (actual time=0.031..0.032 rows=5 loops=1) || Index Cond: (val = 100) || Planning Time: 0.371 ms || Execution Time: 46.063 ms |+---------------------------------------------------------------------------------------------------------------------------------------+-- 使用了 index.set enable_seqscan=off; explain (analyze,timing,costs)select t1.* from it201010a t1 left join it201010a t2 on (t1.id = t2.id and t2.val = 100) where t2.* is null;+-------------------------------------------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+-------------------------------------------------------------------------------------------------------------------------------------------------+| Merge Left Join (cost=40.57..3340.72 rows=500 width=8) (actual time=3.756..55.155 rows=99995 loops=1) || Merge Cond: (t1.id = t2.id) || Filter: (t2.* IS NULL) || Rows Removed by Filter: 5 || -> Index Scan using it201010a_pkey on it201010a t1 (cost=0.29..3050.29 rows=100000 width=8) (actual time=0.054..27.778 rows=100000 loops=1) || -> Sort (cost=40.28..40.30 rows=10 width=36) (actual time=3.692..3.694 rows=5 loops=1) || Sort Key: t2.id || Sort Method: quicksort Memory: 25kB || -> Bitmap Heap Scan on it201010a t2 (cost=4.50..40.11 rows=10 width=36) (actual time=0.252..0.360 rows=5 loops=1) || Recheck Cond: (val = 100) || Heap Blocks: exact=5 || -> Bitmap Index Scan on it201010a_val_idx (cost=0.00..4.49 rows=10 width=0) (actual time=0.128..0.128 rows=5 loops=1) || Index Cond: (val = 100) || Planning Time: 1.516 ms || Execution Time: 65.098 ms |+-------------------------------------------------------------------------------------------------------------------------------------------------+set enable_sort=off; explain (analyze,timing,costs)select t1.* from it201010a t1 left join it201010a t2 on (t1.id = t2.id and t2.val = 100) where t2.* is null; +-------------------------------------------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+-------------------------------------------------------------------------------------------------------------------------------------------------+| Hash Left Join (cost=40.53..3353.04 rows=500 width=8) (actual time=0.153..47.913 rows=99995 loops=1) || Hash Cond: (t1.id = t2.id) || Filter: (t2.* IS NULL) || Rows Removed by Filter: 5 || -> Index Scan using it201010a_pkey on it201010a t1 (cost=0.29..3050.29 rows=100000 width=8) (actual time=0.019..21.066 rows=100000 loops=1) || -> Hash (cost=40.11..40.11 rows=10 width=36) (actual time=0.088..0.089 rows=5 loops=1) || Buckets: 1024 Batches: 1 Memory Usage: 9kB || -> Bitmap Heap Scan on it201010a t2 (cost=4.50..40.11 rows=10 width=36) (actual time=0.032..0.043 rows=5 loops=1) || Recheck Cond: (val = 100) || Heap Blocks: exact=5 || -> Bitmap Index Scan on it201010a_val_idx (cost=0.00..4.49 rows=10 width=0) (actual time=0.019..0.019 rows=5 loops=1) || Index Cond: (val = 100) || Planning Time: 0.331 ms || Execution Time: 55.753 ms |+-------------------------------------------------------------------------------------------------------------------------------------------------+(14 rows)set enable_hashjoin=off; explain (analyze,timing,costs)select t1.* from it201010a t1 left join it201010a t2 on (t1.id = t2.id and t2.val = 100) where t2.* is null; +-------------------------------------------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+-------------------------------------------------------------------------------------------------------------------------------------------------+| Merge Left Join (cost=0.58..6600.71 rows=500 width=8) (actual time=4.255..61.982 rows=99995 loops=1) || Merge Cond: (t1.id = t2.id) || Filter: (t2.* IS NULL) || Rows Removed by Filter: 5 || -> Index Scan using it201010a_pkey on it201010a t1 (cost=0.29..3050.29 rows=100000 width=8) (actual time=0.011..19.161 rows=100000 loops=1) || -> Index Scan using it201010a_pkey on it201010a t2 (cost=0.29..3300.29 rows=10 width=36) (actual time=4.237..18.359 rows=5 loops=1) || Filter: (val = 100) || Rows Removed by Filter: 99995 || Planning Time: 0.348 ms || Execution Time: 69.319 ms |+-------------------------------------------------------------------------------------------------------------------------------------------------+(10 rows)---------set enable_hashjoin=on; set enable_sort=on; set enable_seqscan=on;---------- 2 没有 PK 的情况create table it201010b ( val int not null);insert into it201010bselect random() * 10000 from generate_series(1,1e5); create index on it201010b(val);commit;analyze it201010b;-- 没有PK,我们可以用系统的ctidexplain (analyze,timing,costs)select t1.* from it201010b t1 left join it201010b t2 on (t1.ctid = t2.ctid and t2.val = 100) where t2.* is null;+---------------------------------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+---------------------------------------------------------------------------------------------------------------------------------------+| Hash Left Join (cost=40.24..1858.34 rows=500 width=4) (actual time=0.196..39.142 rows=99991 loops=1) || Hash Cond: (t1.ctid = t2.ctid) || Filter: (t2.* IS NULL) || Rows Removed by Filter: 9 || -> Seq Scan on it201010b t1 (cost=0.00..1443.00 rows=100000 width=10) (actual time=0.018..16.793 rows=100000 loops=1) || -> Hash (cost=40.11..40.11 rows=10 width=34) (actual time=0.117..0.118 rows=9 loops=1) || Buckets: 1024 Batches: 1 Memory Usage: 9kB || -> Bitmap Heap Scan on it201010b t2 (cost=4.50..40.11 rows=10 width=34) (actual time=0.091..0.108 rows=9 loops=1) || Recheck Cond: (val = 100) || Heap Blocks: exact=9 || -> Bitmap Index Scan on it201010b_val_idx (cost=0.00..4.49 rows=10 width=0) (actual time=0.051..0.051 rows=9 loops=1) || Index Cond: (val = 100) || Planning Time: 0.223 ms || Execution Time: 47.081 ms |+---------------------------------------------------------------------------------------------------------------------------------------+------------ 3 Array create table it201010c ( id int generated always as identity, arr int[] not null); do $$declare i int;begin for i in 1..10000 loop insert into it201010c(arr) select array_agg(round(random() * 1e3)) from generate_series(1,100); end loop;end;$$ language plpgsql;commit;create index on it201010c using gin(arr);analyze it201010c;--explain (analyze,timing,costs)select * from it201010c where not (arr @> array[1,2]); +--------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+--------------------------------------------------------------------------------------------------------------+| Seq Scan on it201010c (cost=0.00..714.00 rows=9913 width=428) (actual time=0.027..12.739 rows=9921 loops=1) || Filter: (NOT (arr @> '{1,2}'::integer[])) || Rows Removed by Filter: 79 || Planning Time: 3.535 ms || Execution Time: 13.665 ms |+--------------------------------------------------------------------------------------------------------------+-- 一样用 left join , null explain (analyze,timing,costs)select t1.* from it201010c t1 left join it201010c t2 on (t1.id = t2.id and t2.arr @> array[1,2]) where t2.* is null;+-----------------------------------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+-----------------------------------------------------------------------------------------------------------------------------------------+| Hash Left Join (cost=259.06..986.43 rows=50 width=428) (actual time=4.541..10.398 rows=9921 loops=1) || Hash Cond: (t1.id = t2.id) || Filter: (t2.* IS NULL) || Rows Removed by Filter: 79 || -> Seq Scan on it201010c t1 (cost=0.00..689.00 rows=10000 width=428) (actual time=0.008..2.415 rows=10000 loops=1) || -> Hash (cost=257.97..257.97 rows=87 width=456) (actual time=4.523..4.524 rows=79 loops=1) || Buckets: 1024 Batches: 1 Memory Usage: 46kB || -> Bitmap Heap Scan on it201010c t2 (cost=20.67..257.97 rows=87 width=456) (actual time=4.366..4.480 rows=79 loops=1) || Recheck Cond: (arr @> '{1,2}'::integer[]) || Heap Blocks: exact=76 || -> Bitmap Index Scan on it201010c_arr_idx (cost=0.00..20.65 rows=87 width=0) (actual time=4.325..4.326 rows=79 loops=1) || Index Cond: (arr @> '{1,2}'::integer[]) || Planning Time: 3.118 ms || Execution Time: 11.486 ms |+-----------------------------------------------------------------------------------------------------------------------------------------+-- 利用 not existsexplain (analyze,timing,costs)select t1.* from it201010c t1 where not exists ( select 1 from it201010c t2 where t1.id = t2.id and t2.arr @> array[1,2]);+-----------------------------------------------------------------------------------------------------------------------------------------+| QUERY PLAN |+-----------------------------------------------------------------------------------------------------------------------------------------+| Hash Anti Join (cost=259.06..1073.54 rows=9913 width=428) (actual time=0.404..7.478 rows=9921 loops=1) || Hash Cond: (t1.id = t2.id) || -> Seq Scan on it201010c t1 (cost=0.00..689.00 rows=10000 width=428) (actual time=0.008..2.913 rows=10000 loops=1) || -> Hash (cost=257.97..257.97 rows=87 width=4) (actual time=0.374..0.375 rows=79 loops=1) || Buckets: 1024 Batches: 1 Memory Usage: 11kB || -> Bitmap Heap Scan on it201010c t2 (cost=20.67..257.97 rows=87 width=4) (actual time=0.233..0.342 rows=79 loops=1) || Recheck Cond: (arr @> '{1,2}'::integer[]) || Heap Blocks: exact=76 || -> Bitmap Index Scan on it201010c_arr_idx (cost=0.00..20.65 rows=87 width=0) (actual time=0.215..0.215 rows=79 loops=1) || Index Cond: (arr @> '{1,2}'::integer[]) || Planning Time: 0.259 ms || Execution Time: 8.959 ms |+-----------------------------------------------------------------------------------------------------------------------------------------+没有PK时,一样可以利用 ctid, 在此就不赘述.今天我们探讨了 不等于的情况下, 怎样变化让优化器可以使用index.实际情况还要随着资料分布,index是否具有足够的鉴别性,适当使用 index.另外,也设定了几个参数,让大家可以观察执行计画的情况set enable_hashjoin=on; set enable_sort=on; set enable_seqscan=on;也使用了 array 来探讨,并且使用了 not exists.希望能带给大家在使用时,有一些帮助.
用户投稿
微信扫一扫打赏
支付宝扫一扫打赏
