CODEWITHSUNDEEP
sqlpostgresqlindexing
Marek Příhoda
Marek Příhoda

Reputation: 11198

Would a partial index be used on a query?

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".

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

Upvotes: 2

Views: 276

Answers (1)

roman
roman

Reputation: 117345

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

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

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))

Upvotes: 3

Related Questions