PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15890
PG Version11.2
OSLinux
Opened2019-07-03 09:41:20+00
Reported byAlexey Ermakov
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15890
Logged by:          Alexey Ermakov
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-07-03 09:41:20+00PG Bug reporting formBUG #15890: Planner can't use index "(col) where col is not null" for query "where col in ($1, $2, ... $100+)"
2019-07-03 13:01:41+00David RowleyRe: BUG #15890: Planner can't use index "(col) where col is not null" for query "where col in ($1, $2, ... $100+)"
2019-07-03 13:49:37+00Alexey ErmakovRe: BUG #15890: Planner can't use index "(col) where col is not null" for query "where col in ($1, $2, ... $100+)"