對于一個系統(tǒng)而言，穩(wěn)定性、性能、安全是很重要的幾點(diǎn)。運(yùn)維的一些工作也是圍繞著這些去做。對于某些時候，業(yè)務(wù)層可能會向數(shù)據(jù)庫層提出種種質(zhì)疑：為什么數(shù)據(jù)庫這么慢？為什么數(shù)據(jù)庫掛了？為什么我這么用，SQL走不了索引？諸如此類。

其實(shí)對于了解數(shù)據(jù)庫和運(yùn)維的大家都知道，這些使用關(guān)系型數(shù)據(jù)庫的應(yīng)用系統(tǒng)，SQL語句的好壞會直接影響系統(tǒng)的性能，很多系統(tǒng)性能很差最后發(fā)現(xiàn)都是因?yàn)镾QL寫得很爛的緣故。
有時候可能一條SQL在業(yè)務(wù)設(shè)計之初就存在問題，每次跑的時候每次都走全表掃描，耗費(fèi)大量的系統(tǒng)資源，亦或者在業(yè)務(wù)運(yùn)行到現(xiàn)在的期間內(nèi)數(shù)據(jù)量猛增，數(shù)據(jù)量導(dǎo)致SQL的運(yùn)行結(jié)果遠(yuǎn)遠(yuǎn)大于原來的，導(dǎo)致業(yè)務(wù)受影響。甚至設(shè)計的一些SQL，他本身存在過多的復(fù)雜操作，各種聚合、連接加到一起，這一條SQL跑起來可能就會造成很大的資源占用，甚至嚴(yán)重導(dǎo)致數(shù)據(jù)庫的內(nèi)存溢出、數(shù)據(jù)庫宕機(jī)等等?；蛘邩I(yè)務(wù)層并不了解各種索引的實(shí)際原理，并不能在合適的場景選擇合適的索引，這可能導(dǎo)致，原本SQL查詢對應(yīng)的想要他走索引的字段，沒走索引。從而影響性能。

對所有的關(guān)系型數(shù)據(jù)庫而言，優(yōu)化器無疑是其中最核心的部分，因?yàn)閮?yōu)化器負(fù)責(zé)解析SQL，而我們又都是通過SQL來訪問存儲在關(guān)系型數(shù)據(jù)庫中的數(shù)據(jù)的。所以優(yōu)化器的好壞會直接決定該關(guān)系型數(shù)據(jù)庫的強(qiáng)弱。

通常來說，優(yōu)化器分為兩種，一種是CBO，即Cost-BasedOptimizer 的縮寫，直譯過來就是“ 基于成本的優(yōu)化器”。一種是RBO，是Rule-BasedOptimizer 的縮寫，直譯過來就是“基于規(guī)則的優(yōu)化器”。

在得到最終的執(zhí)行計劃時，RBO會根據(jù)一組內(nèi)置的規(guī)則，去選擇執(zhí)行計劃，這就導(dǎo)致了RBO選擇的執(zhí)行計劃可能不是最優(yōu)的，不是執(zhí)行時間最短的，因?yàn)樗桓鶕?jù)對應(yīng)的規(guī)則去選取執(zhí)行計劃。而CBO所用的判斷原則為成本，CBO會從目標(biāo)SQL諸多可能的執(zhí)行路徑中選擇成本值最小的一條來作為其執(zhí)行計劃。在CBO模式下，由于開銷都是估算值，所以精確性嚴(yán)重依賴于統(tǒng)計信息，如果統(tǒng)計信息越接近表的真實(shí)數(shù)據(jù)時,CBO優(yōu)化器的估算值則越準(zhǔn)確,產(chǎn)生的執(zhí)行計劃也更佳準(zhǔn)確。但是如果統(tǒng)計信息和實(shí)際表數(shù)據(jù)差的很遠(yuǎn)，那么可能通過CBO得出的執(zhí)行計劃也可能不是最優(yōu)的，這個時候就有可能因?yàn)檫@條錯誤的執(zhí)行計劃，引起性能問題或者相關(guān)故障。而目前主流數(shù)據(jù)庫均采用CBO模式，因?yàn)橄噍^于RBO來說，CBO還是更加傾向于得到這個對的執(zhí)行計劃的。

PostgreSQL數(shù)據(jù)庫里也是采用的這種CBO的優(yōu)化器。下面這部分，我就針對上面所說的PostgreSQL里常見的字段有索引但沒有使用索引的現(xiàn)象，進(jìn)行了幾個舉例，供大家參考。對于如下的幾個案例的執(zhí)行計劃選擇，其實(shí)歸根到最后，都是基于CBO得出的代價最小的，或是影響了CBO的判斷得到的最后的結(jié)果。

一、索引列存在多個or連接
二、數(shù)據(jù)量太小
三、選擇性不好
四、查詢條件模糊
五、表的一個列上有重復(fù)索引
六、優(yōu)化器選項(xiàng)關(guān)閉了索引掃描
七、統(tǒng)計信息不準(zhǔn)確
八、Hints影響執(zhí)行計劃
九、查詢條件中使用函數(shù)
十、查詢條件中有不等于運(yùn)算符

一、索引列存在多個or連接

當(dāng)查詢條件中存在多個OR連接時，PostgreSQL需要將所有條件的結(jié)果集進(jìn)行合并，而這個合并操作可能會導(dǎo)致索引失效。

1.模擬環(huán)境

postgres=# create table idxidx as select * from pg_class;
SELECT 445
postgres=# create index idx_11 on idxidx(oid);
CREATE INDEX

2.測試情況

一個or連接兩個索引列（走索引）

postgres=# explain analyze select oid,relname,relnamespace  from idxidx where oid =17726  or oid=17743;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on idxidx  (cost=8.56..14.14 rows=2 width=72) (actual time=0.018..0.019 rows=1 loops=1)
   Recheck Cond: ((oid = '17726'::oid) OR (oid = '17743'::oid))
   Heap Blocks: exact=1
   ->  BitmapOr  (cost=8.56..8.56 rows=2 width=0) (actual time=0.012..0.013 rows=0 loops=1)
         ->  Bitmap Index Scan on idx_11  (cost=0.00..4.28 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=1)
               Index Cond: (oid = '17726'::oid)
         ->  Bitmap Index Scan on idx_11  (cost=0.00..4.28 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
               Index Cond: (oid = '17743'::oid)
 Planning Time: 0.061 ms
 Execution Time: 0.038 ms
(10 rows)

兩個or連接三個索引列（走全表掃描）

postgres=# explain analyze select oid,relname,relnamespace  from idxidx where oid =17726 or oid=17765 or oid=17743;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on idxidx  (cost=0.00..19.79 rows=3 width=72) (actual time=0.012..0.064 rows=1 loops=1)
   Filter: ((oid = '17726'::oid) OR (oid = '17765'::oid) OR (oid = '17743'::oid))
   Rows Removed by Filter: 444
 Planning Time: 0.059 ms
 Execution Time: 0.079 ms
(5 rows)

要避免這種情況，可以嘗試對查詢條件進(jìn)行重寫，例如使用UNION ALL連接多個查詢條件,例如如如下這種方式

postgres=# explain analyze select oid,relname,relnamespace  from idxidx where oid =17726 union all  select oid,relname,relnamespace  from idxidx where oid=17765 union all  select oid,relname,relnamespace  from idxidx where oid=17743;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.27..24.92 rows=3 width=72) (actual time=0.041..0.046 rows=1 loops=1)
   ->  Index Scan using idx_11 on idxidx  (cost=0.27..8.29 rows=1 width=72) (actual time=0.041..0.042 rows=1 loops=1)
         Index Cond: (oid = '17726'::oid)
   ->  Index Scan using idx_11 on idxidx idxidx_1  (cost=0.27..8.29 rows=1 width=72) (actual time=0.002..0.002 rows=0 loops=1)
         Index Cond: (oid = '17765'::oid)
   ->  Index Scan using idx_11 on idxidx idxidx_2  (cost=0.27..8.29 rows=1 width=72) (actual time=0.001..0.001 rows=0 loops=1)
         Index Cond: (oid = '17743'::oid)
 Planning Time: 0.169 ms
 Execution Time: 0.082 ms
(9 rows)

二、數(shù)據(jù)量太小

對于非常小的表或者索引，使用索引可能會比全表掃描更慢。這是因?yàn)槭褂盟饕枰M(jìn)行額外的 I/O 操作，而這些操作可能比直接掃描表更慢。

1.模擬環(huán)境

postgres=# create table tn(id int,name varchar);
CREATE TABLE
postgres=# insert into tn values(1,'ysl');
INSERT 0 1
postgres=# insert into tn values(2,'ysl');
INSERT 0 1
postgres=# insert into tn values(2,'ysll');
INSERT 0 1
postgres=# insert into tn values(2,'ysll');
INSERT 0 1

postgres=# create index idx_tn on tn(id);
CREATE INDEX
postgres=# \d tn
                      Table "public.tn"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id     | integer           |           |          | 
 name   | character varying |           |          | 
Indexes:
    "idx_tn" btree (id)

postgres=# select * from tn;
 id | name 
----+------
  1 | ysl
  2 | ysl
  2 | ysll
  2 | ysll
(4 rows)

2.測試

postgres=# explain analyze select * from tn where id=2;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Seq Scan on tn  (cost=0.00..1.05 rows=1 width=36) (actual time=0.007..0.007 rows=3 loops=1)
   Filter: (id = 2)
   Rows Removed by Filter: 1
 Planning Time: 0.053 ms
 Execution Time: 0.021 ms
(5 rows)

postgres=# explain analyze select * from tn where id=1;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Seq Scan on tn  (cost=0.00..1.05 rows=1 width=36) (actual time=0.011..0.012 rows=1 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 3
 Planning Time: 0.057 ms
 Execution Time: 0.026 ms
(5 rows)

三.選擇性不好

如果索引列中有大量重復(fù)的數(shù)據(jù)，或者一個字段全是一個值，這個時候，索引可能并不能發(fā)揮它的作用，起到加快檢索的作用，因?yàn)檫@個索引并不能顯著地減少需要掃描的行數(shù)，所以計算的代價可能遠(yuǎn)遠(yuǎn)大于走別的執(zhí)行計劃的代價。

基數(shù)：數(shù)據(jù)庫基數(shù)是指數(shù)據(jù)庫中不同值的數(shù)量
select count(distinct column_name) from table_name;

選擇性：基數(shù)和總行數(shù)的比值再乘以100%就是某個列的選擇性。
select count(distinct column_name) /count（column_name）* 100% from table_name;

1.模擬環(huán)境

postgres=# create table tb_t1 as select * from pg_class;
SELECT 465
postgres=# create index idx_tb_t1 on tb_t1(oid);
CREATE INDEX

2.測試

可以看到，原本oid這一列，選擇性較好，分布較均勻的時候，可以正常使用到索引。而選擇性不好的情況下，則

postgres=# explain analyze select * from tb_t1 where oid=17726;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tb_t1  (cost=4.29..9.86 rows=2 width=236) (actual time=0.024..0.025 rows=1 loops=1)
   Recheck Cond: (oid = '17726'::oid)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_tb_t1  (cost=0.00..4.29 rows=2 width=0) (actual time=0.021..0.021 rows=1 loops=1)
         Index Cond: (oid = '17726'::oid)
 Planning Time: 0.220 ms
 Execution Time: 0.059 ms
(7 rows)

postgres=# update tb_t1 set oid=1;
UPDATE 465
postgres=# reindex  index idx_tb_t1;
REINDEX

postgres=# explain analyze select * from tb_t1 where oid=1;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on tb_t1  (cost=0.00..29.81 rows=465 width=274) (actual time=0.013..0.080 rows=465 loops=1)
   Filter: (oid = '1'::oid)
 Planning Time: 0.344 ms
 Execution Time: 0.111 ms
(4 rows)

上邊的這個例子，在我做完update后，列的基數(shù)是select count(distinct oid) from tb_t1;也就是1。而選擇性是select count(distinct oid)/count(oid)* 100% from tb_t1;也就是1/465 *100% 選擇性特別低。索引不能起到減少掃描的行數(shù)，反而在原本的基礎(chǔ)上多了回表的動作，代價就增多了。因此CBO沒有選擇走這個索引的執(zhí)行計劃。

四、查詢條件模糊

如果查詢條件模糊，例如使用了不等于（<>）、LIKE等運(yùn)算符或者使用了函數(shù)等，那么索引可能無法被使用。

因?yàn)檎Ｇ闆r下，等于（=）操作符可以直接利用B-tree或哈希索引進(jìn)行查找。這是因?yàn)?，這些操作符只需要在索引樹中查找與給定值相等的項(xiàng)，就可以快速地定位到符合條件的記錄。

而不等于（<>）操作符則需要查找所有不符合條件的記錄，這會導(dǎo)致需要遍歷整個索引樹來找到匹配的記錄，因此使用索引的成本比全表掃描更高。
LIKE操作符也可能導(dǎo)致不使用索引。這是因?yàn)椋琇IKE操作符通常需要執(zhí)行模糊匹配，即查找包含你給的關(guān)鍵字的記錄。雖然可以使用B-tree索引進(jìn)行模糊匹配，但是如果模式以通配符開頭（例如’%abc’），則索引將不會被使用，因?yàn)檫@種情況下需要遍歷整個索引樹來查找符合條件的記錄。

這兩種方式在列上有索引的時候，都是不能顯著地減少需要掃描的行數(shù)。甚至加大SQL執(zhí)行的代價，因此可能上邊的索引不會被CBO選擇為最后最優(yōu)的執(zhí)行計劃。

1.模擬環(huán)境

postgres=# create table tb_l1 as select * from pg_class;
SELECT 465
postgres=# create index idx_tb_l1 on tb_l1(oid);
CREATE INDEX

2.測試

postgres=# explain analyze select * from tb_l1 where oid=17726;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tb_l1 on tb_l1  (cost=0.27..8.29 rows=1 width=274) (actual time=0.029..0.030 rows=1 loops=1)
   Index Cond: (oid = '17726'::oid)
 Planning Time: 0.473 ms
 Execution Time: 0.083 ms
(4 rows)

postgres=# explain analyze select * from tb_l1 where oid<>17726;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on tb_l1  (cost=0.00..17.81 rows=464 width=274) (actual time=0.007..0.103 rows=464 loops=1)
   Filter: (oid <> '17726'::oid)
   Rows Removed by Filter: 1
 Planning Time: 0.069 ms
 Execution Time: 0.132 ms
(5 rows)

五、表的一個列上有重復(fù)索引

在PostgreSQL里，是允許在一列上建立多個索引的，也就是如下這種方式，是不會報錯說索引重復(fù)的，這也就導(dǎo)致了，使用過程中表上可能存在多余的重復(fù)索引，索引不會全部被使用到，而且可能引起性能問題。

postgres=# create index idx_tb_l1 on tb_l1(oid);
CREATE INDEX
postgres=# create index idx_tb_l2 on tb_l1(oid);
CREATE INDEX

1.模擬環(huán)境

postgres=# create table tb_l1 as select * from pg_class;
SELECT 465
postgres=# create index idx_tb_l1 on tb_l1(oid);
CREATE INDEX
postgres=# create index idx_tb_l2 on tb_l1(oid);
CREATE INDEX
postgres=# \d tb_l1
                        Table "public.tb_l1"
       Column        |     Type     | Collation | Nullable | Default 
---------------------+--------------+-----------+----------+---------
 oid                 | oid          |           |          | 
 relname             | name         |           |          | 
 relnamespace        | oid          |           |          | 
 reltype             | oid          |           |          | 
 reloftype           | oid          |           |          | 
 relowner            | oid          |           |          | 
 ... ...
 ... ...
Indexes:
    "idx_tb_l1" btree (oid)
    "idx_tb_l2" btree (oid)

2.測試

postgres=# explain analyze select * from tb_l1 where oid=17726;                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tb_l2 on tb_l1  (cost=0.27..8.29 rows=1 width=274) (actual time=0.025..0.025 rows=1 loops=1)
   Index Cond: (oid = '17726'::oid)
 Planning Time: 0.364 ms
 Execution Time: 0.043 ms
(4 rows)

測試可以看到，在一個表的同一列上的兩個索引其實(shí)作用是一樣的，僅僅名字不一樣，屬于重復(fù)索引，這種情況下，就算用到索引，同一時刻也就會使用到一個索引。

使用如下的SQL可以找到數(shù)據(jù)庫里的重復(fù)索引，可以定期巡檢的時候進(jìn)行檢查，并在確認(rèn)后合理優(yōu)化掉重復(fù)的索引

SELECT
  indrelid :: regclass              AS table_name,
  array_agg(indexrelid :: regclass) AS indexes
FROM pg_index
GROUP BY
  indrelid, indkey
HAVING COUNT(*) > 1;

一個執(zhí)行的結(jié)果如下所示：

postgres=# SELECT
  indrelid :: regclass              AS table_name,
  array_agg(indexrelid :: regclass) AS indexes
FROM pg_index
GROUP BY
  indrelid, indkey
HAVING COUNT(*) > 1;
 table_name |        indexes        
------------+-----------------------
 tb_l1      | {idx_tb_l1,idx_tb_l2}
 t1         | {ind1,idx2}
(2 rows)

postgres=# \di+ idx_tb_l1 
                                        List of relations
 Schema |   Name    | Type  |  Owner  | Table | Persistence | Access method | Size  | Description 
--------+-----------+-------+---------+-------+-------------+---------------+-------+-------------
 public | idx_tb_l1 | index | xmaster | tb_l1 | permanent   | btree         | 32 kB | 
(1 row)

postgres=# \di+ idx_tb_l2 
                                        List of relations
 Schema |   Name    | Type  |  Owner  | Table | Persistence | Access method | Size  | Description 
--------+-----------+-------+---------+-------+-------------+---------------+-------+-------------
 public | idx_tb_l2 | index | xmaster | tb_l1 | permanent   | btree         | 32 kB | 
(1 row)

六、優(yōu)化器選項(xiàng)關(guān)閉了索引掃描

PostgreSQL里有著很多的可以影響優(yōu)化器的參數(shù)，例如enable_indexscan,enable_bitmapscan,enable_hashjoin,enable_sort等等，這些參數(shù)可以在session，用戶，數(shù)據(jù)庫級別進(jìn)行設(shè)置?？梢酝ㄟ^設(shè)置這些參數(shù)的值，來改變相關(guān)SQL執(zhí)行時的執(zhí)行計劃。但是需要注意的是，為了個別的SQL，去盲目改變這些參數(shù)的值，往往是得不償失的，操作的時候需要嚴(yán)謹(jǐn)并且仔細(xì)考慮，否則，這些類型的參數(shù)的改變，對于數(shù)據(jù)庫的性能影響可能是巨大的。

1.模擬環(huán)境

postgres=# create table tb_l1 as select * from pg_class;
SELECT 465
postgres=# create index idx_tb_l1 on tb_l1(oid);
CREATE INDEX

2.測試

開啟了對應(yīng)優(yōu)化器選項(xiàng)

postgres=# show enable_indexscan ;
 enable_indexscan 
------------------
 on
(1 row)

postgres=# show enable_bitmapscan ;
 enable_bitmapscan 
-------------------
 on
(1 row)

postgres=# explain analyze select * from tb_l1 where oid=17721;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tb_l2 on tb_l1  (cost=0.27..8.29 rows=1 width=274) (actual time=0.017..0.018 rows=1 loops=1)
   Index Cond: (oid = '17721'::oid)
 Planning Time: 0.088 ms
 Execution Time: 0.038 ms
(4 rows)

關(guān)閉對應(yīng)的優(yōu)化器選項(xiàng)，可以看到CBO受到設(shè)置的參數(shù)的影響，選擇了seq scan的執(zhí)行計劃，而沒有用到字段上的索引。

postgres=# set enable_indexscan=off;
SET
postgres=# set enable_bitmapscan=off;
SET
postgres=# explain analyze select * from tb_l1 where oid=17721;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on tb_l1  (cost=0.00..17.81 rows=1 width=274) (actual time=0.024..0.137 rows=1 loops=1)
   Filter: (oid = '17721'::oid)
   Rows Removed by Filter: 464
 Planning Time: 0.079 ms
 Execution Time: 0.192 ms
(5 rows)

七、統(tǒng)計信息不準(zhǔn)確

因?yàn)镃BO本身是基于代價的優(yōu)化器，而計算代價要根據(jù)統(tǒng)計信息去做計算，統(tǒng)計信息不準(zhǔn)確，得到的執(zhí)行計劃可能不是最優(yōu)，這一點(diǎn)不做具體的舉例。

八、Hints影響執(zhí)行計劃

PostgreSQL數(shù)據(jù)庫里有著像ORACLE里類似的Hints功能，即pg_hint_plan工具，用Hints能夠改變sql語句的執(zhí)行計劃，hint就是優(yōu)化器的一種指示。雖然功能上和效果是類似的，但是PostgreSQL和ORACLE的Hints并不完全一致的，例如全表掃描等的關(guān)鍵字是不同的，需要進(jìn)行區(qū)分。

1、準(zhǔn)備環(huán)境

數(shù)據(jù)庫需安裝pg_hint_plan插件

postgres=# create table test_hint(id int,c varchar(100));
CREATE TABLE
postgres=# insert into test_hint select i,'test'||i from generate_series(1,10000) i;
INSERT 0 10000
postgres=# create index idx_test_hint_id on test_hint(id);
CREATE INDEX

2、測試

默認(rèn)會走索引掃描，但是使用了hint，讓其走了seqscan，沒有使用到對應(yīng)的字段上的索引。

postgres=# explain analyze select * from test_hint where id=10;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_test_hint_id on test_hint  (cost=0.29..8.30 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=1)
   Index Cond: (id = 10)
 Planning Time: 0.111 ms
 Execution Time: 0.024 ms
(4 rows)

postgres=# explain analyze select /*+seqscan(t) */ * from test_hint t where id=10;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on test_hint t  (cost=0.00..180.00 rows=1 width=12) (actual time=0.022..2.691 rows=1 loops=1)
   Filter: (id = 10)
   Rows Removed by Filter: 9999
 Planning Time: 0.311 ms
 Execution Time: 2.712 ms
(5 rows)

九、查詢條件中使用函數(shù)

當(dāng)查詢條件中包含函數(shù)調(diào)用時，PostgreSQL里可能無法使用索引，因?yàn)樗枰獙λ袛?shù)據(jù)進(jìn)行計算，而不是只計算索引值。

1、準(zhǔn)備環(huán)境

postgres=# CREATE TABLE test_table (
postgres(#     id SERIAL PRIMARY KEY,
postgres(#     name TEXT,
postgres(#     age INTEGER
postgres(# );
CREATE TABLE
postgres=# 
postgres=# CREATE INDEX age_index ON test_table(age);
CREATE INDEX
postgres=# INSERT INTO test_table (name, age) VALUES 
postgres-#     ('Alice', 25),
postgres-#     ('Bob', 30),
postgres-#     ('Charlie', 35),
postgres-#     ('David', 40),
postgres-#     ('Eve', 45),
postgres-#     ('Frank', 50);
INSERT 0 6
postgres=# CREATE OR REPLACE FUNCTION search_age(p_age INTEGER) RETURNS SETOF test_table AS $$
postgres$# BEGIN
postgres$#     RETURN QUERY SELECT * FROM test_table WHERE age > p_age;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION

2、測試

可以看到，當(dāng)查詢條件中包含函數(shù)調(diào)用時，沒有使用到索引，而是使用了一個 Function Scan。這個Function Scan也是一種特殊的掃描方式，是從函數(shù)中獲取數(shù)據(jù)。PostgreSQL會調(diào)用指定的函數(shù)來處理查詢結(jié)果，并且會為函數(shù)的輸出結(jié)果創(chuàng)建一個虛擬的關(guān)系表，以便后續(xù)的節(jié)點(diǎn)可以使用這個關(guān)系表繼續(xù)執(zhí)行查詢。

postgres=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE age > 35;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=7.25..22.25 rows=400 width=40) (actual time=0.008..0.009 rows=3 loops=1)
   Recheck Cond: (age > 35)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on age_index  (cost=0.00..7.15 rows=400 width=0) (actual time=0.004..0.004 rows=3 loops=1)
         Index Cond: (age > 35)
 Planning Time: 0.075 ms
 Execution Time: 0.027 ms
(7 rows)

postgres=# EXPLAIN ANALYZE SELECT * FROM search_age(35);
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Function Scan on search_age  (cost=0.25..10.25 rows=1000 width=40) (actual time=0.147..0.147 rows=3 loops=1)
 Planning Time: 0.027 ms
 Execution Time: 0.162 ms
(3 rows)

十、查詢條件中有不等于運(yùn)算符

因?yàn)樵谒饕龗呙杵陂g，不等于運(yùn)算符會導(dǎo)致索引中的每一行都需要進(jìn)行比較，因此需要走全表掃描，不會走索引。文章來源地址http://www.zghlxwxcb.cn/news/detail-553723.html

1.環(huán)境準(zhǔn)備

postgres=# create table tb_l1 as select * from pg_class;
SELECT 465
postgres=# create index idx_tb_l1 on tb_l1(oid);
CREATE INDEX

2.測試

postgres=# explain analyze select * from tb_l1 where oid<>17721;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on tb_l1  (cost=0.00..17.81 rows=464 width=274) (actual time=0.007..0.063 rows=464 loops=1)
   Filter: (oid <> '17721'::oid)
   Rows Removed by Filter: 1
 Planning Time: 0.064 ms
 Execution Time: 0.091 ms
(5 rows)

postgres=# explain analyze select * from tb_l1 where oid =17721;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tb_l2 on tb_l1  (cost=0.27..8.29 rows=1 width=274) (actual time=0.019..0.021 rows=1 loops=1)
   Index Cond: (oid = '17721'::oid)
 Planning Time: 0.107 ms
 Execution Time: 0.051 ms
(4 rows)

postgres=# explain analyze select * from tb_l1 where oid !=17721;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on tb_l1  (cost=0.00..17.81 rows=464 width=274) (actual time=0.012..0.072 rows=464 loops=1)
   Filter: (oid <> '17721'::oid)
   Rows Removed by Filter: 1
 Planning Time: 0.071 ms
 Execution Time: 0.102 ms
(5 rows)

到了這里，關(guān)于【PG里常見的字段有索引但沒有使用索引的原因】的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！