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 10beta2 on 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)
Workers Planned: 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 on test (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 Scan on 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_prefix on 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#L644
vardata->statsTuple is zero for incorrect estimations. 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