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
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 |