当我可以在Seq scan命令中看到部分索引时，为什么会得到\d+；
\d+ call_records;
id | integer | not null default nextval('call_records_id_seq'::regclass) | plain | |
plain_crn | bigint |
active | boolean | default true
timestamp | bigint | default 0
Indexes:
"index_call_records_on_plain_crn" UNIQUE, btree (plain_crn)
"index_call_records_on_active" btree (active) WHERE active = true
正如预期的那样，
id是一个索引扫描。EXPLAIN select * from call_records where id=1;
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using call_records_pkey on call_records (cost=0.14..8.16 rows=1 width=373)
Index Cond: (id = 1)
(2 rows)
普通的也一样
EXPLAIN select * from call_records where plain_crn=1;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Index Scan using index_call_records_on_plain_crn on call_records (cost=0.14..8.16 rows=1 width=373)
Index Cond: (plain_crn = 1)
(2 rows)
但是，在
active的情况下就不一样了。EXPLAIN select * from call_records where active=true; QUERY PLAN
-----------------------------------------------------------------
Seq Scan on call_records (cost=0.00..12.00 rows=100 width=373)
Filter: active
(2 rows)
最佳答案
PostgreSQL是否使用“active”上的索引取决于真与假的比率。在某些情况下，如果真的比假的多，查询规划器将决定表扫描可能会更快。
我建立了一个测试表，并加载了一百万行随机（ish）数据。
select active, count(*)
from call_records
group by active;
active count -- f 499983 t 500017
True and false have roughly the same number of rows. Here's the execution plan.
explain analyze
select * from call_records where active=true;
"Bitmap Heap Scan on call_records (cost=5484.82..15344.49 rows=500567 width=21) (actual time=56.542..172.084 rows=500017 loops=1)" " Filter: active" " Heap Blocks: exact=7354" " -> Bitmap Index Scan on call_records_active_idx (cost=0.00..5359.67 rows=250567 width=0) (actual time=55.040..55.040 rows=500023 loops=1)" " Index Cond: (active = true)" "Planning time: 0.105 ms" "Execution time: 204.209 ms"
Then I updated "active", updated the statistics, and checked again.
update call_records
set active = true
where id < 750000;
analyze call_records;
explain analyze
select * from call_records where active=true;
"Seq Scan on call_records (cost=0.00..22868.00 rows=874100 width=21) (actual time=0.032..280.506 rows=874780 loops=1)" " Filter: active" " Rows Removed by Filter: 125220" "Planning time: 0.316 ms" "Execution time: 337.400 ms"
Turning off sequential scans shows that, in my case, PostgreSQL made the right decision. The table scan (sequential scan) was about 10 ms faster.
set enable_seqscan = off;
explain analyze
select * from call_records where active=true;
“使用call_records_active_idx on call_records（cost=0.42..39071.14 rows=874100 width=21）进行索引扫描（实际时间=0.031..293.295 rows=874780 loops=1）”
“索引条件：（活动=真）”
“计划时间：0.343 ms”
“执行时间：349.403 ms”
关于postgresql - 部分索引未生效，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38169683/