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 <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: alexey(dot)ermakov(at)dataegret(dot)com
Subject: BUG #15890: Planner can't use index "(col) where col is not null" for query "where col in ($1, $2, ... $100+)"
Date: 2019-07-03 09:41:20
Message-ID: 15890-d9e1cb33aceb86aa@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: 15890
Logged by: Alexey Ermakov
Email address: alexey(dot)ermakov(at)dataegret(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Juan José Santamaría Flecha 2019-07-03 10:10:44 Re: BUG #15889: PostgreSQL failed to build due to error MSB8020 with MSVC on windows
Previous Message PG Bug reporting form 2019-07-03 09:10:34 BUG #15889: PostgreSQL failed to build due to error MSB8020 with MSVC on windows