[BUGS] BUG #14753: Bad selectivity estimation with functional partial index

The following bug has been logged on the website:

Bug reference:      14753
Logged by:          Dmitry Lazurkin
Email address:      dilaz03@gmail.com
PostgreSQL version: 10beta2
Operating system:   Ubuntu 5.4.0-6ubuntu1~16.04.4
Description:

Short example session:

select version();                                                     version

-------------------------------------------------------------------------------------------------------------------PostgreSQL
10beta2on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
 
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)



-- Fill database
create table test as select 'first' as name from generate_series(1,
1000000)
n;
insert into test (select 'second' as name from generate_series(0,
1000000));
insert into test (select 'third' as name from generate_series(0,
1000000));
analyze test;



explain select * from test where name ~~ '%120%';
-- Estimated rows: 1 (correct)                             QUERY PLAN
-----------------------------------------------------------------------Gather  (cost=1000.00..29900.11 rows=1 width=6)
WorkersPlanned: 2  ->  Parallel Seq Scan on test  (cost=0.00..28900.01 rows=1 width=6)        Filter: (name ~~
'%120%'::text)
(4 rows)



create extension if not exists pg_trgm;
create index test_upper_trgm_without_prefix on test using gist
(upper(name)
gist_trgm_ops) where name !~~ '$$%';
analyze test;
explain select * from test where name !~~ '$$%' and upper(name) ~~
'%120%';
-- Estimated rows: 120000 (!!!INCORRECT)                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------Bitmap Heap Scan
ontest  (cost=6002.41..21377.41 rows=120000 width=6)  Recheck Cond: ((upper(name) ~~ '%120%'::text) AND (name !~~
 
'$$%'::text))  ->  Bitmap Index Scan on test_upper_trgm_without_prefix 
(cost=0.00..5972.41 rows=120000 width=0)        Index Cond: (upper(name) ~~ '%120%'::text)
(4 rows)



drop index test_upper_trgm_without_prefix;
create index test_upper_trgm_without_upper_prefix on test using gist
(upper(name) gist_trgm_ops) where upper(name) !~~ '$$%';
analyze test;
explain select * from test where upper(name) !~~ '$$%' and upper(name) ~~
'%120%';
-- Estimated rows: 119400 (!!!INCORRECT)                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------Bitmap Heap
Scanon test  (cost=5973.76..21636.76 rows=119400 width=6)  Recheck Cond: ((upper(name) ~~ '%120%'::text) AND
(upper(name)!~~
 
'$$%'::text))  ->  Bitmap Index Scan on test_upper_trgm_without_upper_prefix 
(cost=0.00..5943.91 rows=119400 width=0)        Index Cond: (upper(name) ~~ '%120%'::text)
(4 rows)



create index test_upper_trgm on test using gist (upper(name)
gist_trgm_ops);
analyze test;
explain select * from test where upper(name) !~~ '$$%' and upper(name) ~~
'%120%';
-- Estimated rows: 1 (!!!CORRECT)                                          QUERY PLAN
-------------------------------------------------------------------------------------------------Index Scan using
test_upper_trgm_without_upper_prefixon test 
 
(cost=0.41..8.43 rows=1 width=6)  Index Cond: (upper(name) ~~ '%120%'::text)
(2 rows)


Postgres doesn't see corresponding stats tuple for functional partial
index.
On
https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/selfuncs.c#L644vardata->statsTuple is zero for
incorrectestimations. Only works with 
just
functional index. I think this problem exists with all indexes (not
pg_trgm+gist).

PS. May be this problem is duplication of BUG #8598.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs