Some update now with full reproducible test case (and some surprising
results):
Test case initialization:
drop table if exists test;
create table test as (select g.i as id, (random()*100)::integer as
is_finished from generate_series(1,1000000) as g(i));
create index test_1_key on test(id, is_finished) where is_finished =3D ANY
(ARRAY[0, 5]);
vacuum analyze test;
Good (but not expected in that case) plan:
explain analyze select * from test where is_finished=3D0 or is_finished=3D5
order by id limit 1;
QUERY PLAN
---------------------------------------------------------------------------=
--------------------------------------------------------
Limit (cost=3D0.00..0.24 rows=3D1 width=3D8) (actual time=3D0.052..0.052 =
rows=3D1
loops=3D1)
-> Index Only Scan using test_1_key on test (cost=3D0.00..4493.05
rows=3D18921 width=3D8) (actual time=3D0.052..0.052 rows=3D1 loops=3D1)
Heap Fetches: 1
Total runtime: 0.066 ms
(i'm very surprised than the PostgreSQL managed deduct is_finished =3D ANY
(ARRAY[0, 5]) from (is_finished=3D0 or is_finished=3D5))
Bad plan (techically the same query and even better suitable for the
partial index and should have the same plan but no luck):
explain analyze select * from test where is_finished IN (0,5) order by id
limit 1;
QUERY PLA=
N
---------------------------------------------------------------------------=
-------------------------------------------------------------------
Limit (cost=3D4809.18..4809.19 rows=3D1 width=3D8) (actual time=3D15.410.=
.15.410
rows=3D1 loops=3D1)
-> Sort (cost=3D4809.18..4999.44 rows=3D19026 width=3D8) (actual
time=3D15.408..15.408 rows=3D1 loops=3D1)
Sort Key: id
Sort Method: top-N heapsort Memory: 25kB
-> Index Only Scan using test_1_key on test (cost=3D0.00..4428.6=
6
rows=3D19026 width=3D8) (actual time=3D0.051..12.277 rows=3D15222 loops=3D1=
)
Index Cond: (is_finished =3D ANY ('{0,5}'::integer[]))
Heap Fetches: 15222
Total runtime: 15.469 ms
--=20
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.com/=
>
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com
=D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: http://mboguk.moikrug.ru/
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."
