From: | maxim(dot)boguk(at)postgresql-consulting(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #11441: Weird (and seems wrong) behavior of partial indexes with order by/limit |
Date: | 2014-09-17 05:57:23 |
Message-ID: | 20140917055723.2495.99190@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 11441
Logged by: Maksym Boguk
Email address: maxim(dot)boguk(at)postgresql-consulting(dot)com
PostgreSQL version: 9.2.9
Operating system: Linux
Description:
Today I found very strange behavior of partial indexes with correlating
order by/limit.
Simple test case look like:
(postgres(at)[local]:5432)=# \d qqq_test
Table "public.qqq_test"
Column | Type | Modifiers
-------------+---------+-----------
resume_id | integer |
is_finished | integer |
10M rows, vacuumed and analyzed
1.Initial query (no problem found):
select * from qqq_test where is_finished=0 order by resume_id limit 1;
with index:
create index qqq_test_1_key on qqq_test(resume_id, is_finished) where
is_finished=0;
there are no issue:
(postgres(at)[local]:5432)=# explain analyze select * from qqq_test where
is_finished=0 order by resume_id limit 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.32 rows=1 width=8) (actual time=0.062..0.062 rows=1
loops=1)
-> Index Only Scan using qqq_test_1_key on qqq_test
(cost=0.00..44145.36 rows=138808 width=8) (actual time=0.062..0.062 rows=1
loops=1)
Index Cond: (is_finished = 0)
Total runtime: 0.035 ms
(index only scan as expected).
2.Now a bit more complicated case:
select * from qqq_test where is_finished = ANY (ARRAY[0, 5]) order by
resume_id limit 1;
With index:
create index qqq_test_3_key on qqq_test(resume_id) where (is_finished = ANY
(ARRAY[0, 5]));
also no issue:
(postgres(at)[local]:5432)=# explain analyze select * from qqq_test where
is_finished = ANY (ARRAY[0, 5]) order by resume_id limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.31 rows=1 width=8) (actual time=0.035..0.035 rows=1
loops=1)
-> Index Scan using qqq_test_3_key on qqq_test (cost=0.00..54166.69
rows=173510 width=8) (actual time=0.034..0.034 rows=1 loops=1)
Total runtime: 0.049 ms
(index scan as expected).
3.Now it would be nice to have index only scan for the second query (lets
add is_finished to index description to let IOS be used), and there problem
begin:
drop index qqq_test_3_key;
create index qqq_test_2_key on qqq_test(resume_id, is_finished) where
(is_finished = ANY (ARRAY[0, 5]));
And now oops:
(postgres(at)[local]:5432)=# explain analyze select * from qqq_test where
is_finished = ANY (ARRAY[0, 5]) order by resume_id limit 1;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=38740.23..38740.24 rows=1 width=8) (actual time=54.251..54.251
rows=1 loops=1)
-> Sort (cost=38740.23..40359.65 rows=161942 width=8) (actual
time=54.251..54.251 rows=1 loops=1)
Sort Key: resume_id
Sort Method: top-N heapsort Memory: 25kB
-> Index Only Scan using qqq_test_2_key on qqq_test
(cost=0.00..35501.39 rows=161942 width=8) (actual time=0.030..33.449
rows=145278 loops=1)
Index Cond: (is_finished = ANY ('{0,5}'::integer[]))
Heap Fetches: 0
Total runtime: 54.282 ms
Is there any reason why index scan/index only scan could not be used for
this query instead of slow sort+limit?
It's bug or planner/executor limitation (set enable_sort to 0 - have no
effect)?
Query/index pair looks like pretty suitable for simple IOS without
sort+limit.
Now I cannot think any way to perform such query using IOS because with no
is_finished column in index there will be no IOS, and if I add is_finished
to the index there no index scan but slow order by+limit plan.
Kind Regards,
Maksym
From | Date | Subject | |
---|---|---|---|
Next Message | hmozaffari | 2014-09-17 14:41:11 | BUG #11442: Long binding time for queries on tables with partitions |
Previous Message | galery | 2014-09-16 20:40:04 | BUG #11438: postgresql compiled with gcc/c |