是否会在查询中使用部分索引？

Given this partial index:

鉴于此部分指数:

CREATE INDEX orders_id_created_at_index 
   ON orders(id) WHERE created_at < '2013-12-31';

Would this query use the index?

这个查询会使用索引吗?

SELECT * 
FROM orders 
WHERE id = 123 AND created_at = '2013-10-12';

As per the documentation, "a partial index can be used in a query only if the system can recognize that the WHERE condition of the query mathematically implies the predicate of the index".

根据文档,“只有当系统能够识别出查询的WHERE条件在数学上暗示索引的谓词时,才能在查询中使用部分索引”。

Does that mean that the index will or will not be used?

这是否意味着将使用或不使用索引?

1 个解决方案

#1

You can check and yes, it would be used. I've created sql fiddle to check it with a query like this:

你可以检查,是的,它会被使用。我创建了sql小提琴,用这样的查询检查它:

create table orders(id int, created_at date);

CREATE INDEX orders_id_created_at_index ON orders(id) WHERE created_at < '2013-12-31';

insert into orders
select
    (random()*500)::int, '2013-01-01'::date + ((random() * 200)::int || ' day')::interval
from generate_series(1, 10000) as g

SELECT * FROM orders WHERE id = 123 AND created_at = '2013-10-12';
SELECT * FROM orders WHERE id = 123 AND created_at = '2014-10-12';

sql fiddle demo

sql小提琴演示

If you check execution plans for these queries, you'll see for first query:

如果您检查这些查询的执行计划,您将看到第一个查询:

Bitmap Heap Scan on orders (cost=4.39..40.06 rows=1 width=8) Recheck Cond: ((id = 123) AND (created_at < '2013-12-31'::date)) Filter: (created_at = '2013-10-12'::date)
-> Bitmap Index Scan on orders_id_created_at_index (cost=0.00..4.39 rows=19 width=0) Index Cond: (id = 123)

and for second query:

并为第二个查询:

Seq Scan on orders (cost=0.00..195.00 rows=1 width=8) Filter: ((id = 123) AND (created_at = '2014-10-12'::date))

#1