BUG #14753: Bad selectivity estimation with functional partial index

From: dilaz03(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Cc: dilaz03(at)gmail(dot)com
Subject: BUG #14753: Bad selectivity estimation with functional partial index
Date: 2017-07-19 15:20:38
Message-ID: 20170719152038.19353.71475@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14753
Logged by: Dmitry Lazurkin
Email address: dilaz03(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2017-07-19 15:45:23 Re: BUG #14753: Bad selectivity estimation with functional partial index
Previous Message Tom Lane 2017-07-19 14:21:20 Re: BUG #14750: Seq Scan instead of Index Scan works without limitation