Thread: BUG #15890: Planner can't use index "(col) where col is not null" for query "where col in ($1, $2, ... $100+)"
BUG #15890: Planner can't use index "(col) where col is not null" for query "where col in ($1, $2, ... $100+)"
From
PG Bug reporting form
Date:
The following bug has been logged on the website:
Bug reference: 15890
Logged by: Alexey Ermakov
Email address: alexey.ermakov@dataegret.com
PostgreSQL version: 11.2
Operating system: Linux
Description:
Hello,
please see following test case:
# create table test_not_null_indexes (a int);
CREATE TABLE
# insert into test_not_null_indexes select id from generate_series(1,10000)
gs(id);
INSERT 0 10000
# insert into test_not_null_indexes select null from
generate_series(1,10000) gs(id);
INSERT 0 10000
# create index concurrently on test_not_null_indexes using btree(a) where a
is not null;
CREATE INDEX
# \d+ test_not_null_indexes
Table "public.test_not_null_indexes"
Column | Type | Collation | Nullable | Default | Storage | Stats target
| Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain |
|
Indexes:
"test_not_null_indexes_a_idx" btree (a) WHERE a IS NOT NULL
--up to 100 elements, using partial index as expected
# explain select a from test_not_null_indexes where a in
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100);
-------------------------------------------------------------------------------
Index Only Scan using test_not_null_indexes_a_idx on test_not_null_indexes
(cost=0.29..33.66 rows=100 width=4)
Index Cond: (a = ANY ('{1,2,...,100}'::integer[]))
(2 rows)
-- 100+ elements, can't use index, using seq scan
# explain select a from test_not_null_indexes where a in
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101);
-------------------------------------------------------------------------------
Seq Scan on test_not_null_indexes (cost=0.00..2732.90 rows=101 width=4)
Filter: (a = ANY ('{1,2,...,100,101}'::integer[]))
(2 rows)
--additional condition "a is not null" solves the problem
# explain select a from test_not_null_indexes where a in
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101)
and a is not null;
-----------------------------------------------------------------------------------
Index Only Scan using test_not_null_indexes_a_idx on test_not_null_indexes
(cost=0.29..33.45 rows=50 width=4)
Index Cond: (a = ANY ('{1,2,...,100,101}'::integer[]))
(2 rows)
Looks like magic happens somewhere in predtest.c and MAX_SAOP_ARRAY_SIZE=100
limits number of elements for that case.
Is it possible somehow to pass information that (a) can't be null in such
cases?
--
Thanks,
Alexey Ermakov
Re: BUG #15890: Planner can't use index "(col) where col is not null"for query "where col in ($1, $2, ... $100+)"
From
David Rowley
Date:
On Wed, 3 Jul 2019 at 21:42, PG Bug reporting form <noreply@postgresql.org> wrote: > -- 100+ elements, can't use index, using seq scan > # explain select a from test_not_null_indexes where a in > Is it possible somehow to pass information that (a) can't be null in such > cases? Yes, it is possible, however, this is not a bug in v11. It's something that has been improved in v12, which will be released later this year. See: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=65ce07e020 v11 won't receive the same improvement. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: BUG #15890: Planner can't use index "(col) where col is not null"for query "where col in ($1, $2, ... $100+)"
From
Alexey Ermakov
Date:
great, thank you! -- Alexey Ermakov On 7/3/19 19:01, David Rowley wrote: > On Wed, 3 Jul 2019 at 21:42, PG Bug reporting form > <noreply@postgresql.org> wrote: >> -- 100+ elements, can't use index, using seq scan >> # explain select a from test_not_null_indexes where a in >> Is it possible somehow to pass information that (a) can't be null in such >> cases? > Yes, it is possible, however, this is not a bug in v11. It's something > that has been improved in v12, which will be released later this year. > > See: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=65ce07e020 > > v11 won't receive the same improvement. >