Hi,
Ivan Frolkov reported a problem with choosing a non-optimal index during
a query optimization. This problem appeared after building of an
extended statistics.
I prepared the test case (see t.sql in attachment).
For reproduction of this case we need to have a composite primary key
index and one another index.
Before creation of extended statistics, SELECT from the table choose PK
index and returns only one row. But after, this SELECT picks alternative
index, fetches and filters many tuples.
The problem is related to a corner case in btree cost estimation procedure:
if postgres detects unique one-row index scan, it sets
numIndexTuples to 1.0.
But the selectivity is calculated as usual, by the
clauselist_selectivity() routine and can have a value, much more than
corresponding to single tuple. This selectivity value is used later in
the code to calculate a number of fetched tuples and can lead to
choosing of an suboptimal index.
The attached patch is my suggestion to fix this problem.
--
regards,
Andrey Lepikhov
Postgres Professional