Re: BRIN cost estimate breaks geometric indexes

On 21.01.2020 0:00, Darafei "Komяpa" Praliaskouski wrote:
> Hi,
>
> Found out today that BRIN indexes don't really work for PostGIS and
> box datatypes.
>
> Since
> https://github.com/postgres/postgres/commit/7e534adcdc70866e7be74d626b0ed067c890a251 Postgres
> requires datatype to provide correlation statistics. Such statistics
> wasn't provided by PostGIS and box types.
>
> Today I tried to replace a 200gb gist index with 8mb brin index and
> queries didn't work as expected - it was never used. set
> enable_seqscan=off helped for a bit but that's not a permanent solution.
> Plans for context:
> https://gist.github.com/Komzpa/2cd396ec9b65e2c93341e9934d974826
>
> Debugging session on #postgis IRC channel leads to this ticket to
> create a (not that meaningful) correlation statistics for geometry
> datatype: https://trac.osgeo.org/postgis/ticket/4625#ticket
>
> Postgres Professional mentioned symptoms of the issue in their
> in-depth manual:
> https://habr.com/ru/company/postgrespro/blog/346460/ - box datatype
> showed same unusable BRIN symptoms for them.


(Translated to English:
https://habr.com/en/company/postgrespro/blog/452900/)


> A reasonable course of action on Postgres side seems to be to not
> assume selectivity of 1 in absence of correlation statistics, but
> something that would prefer such an index to a parallel seq scan, but
> higher than similar GIST.
>
> Any other ideas?


As far as I understand, correlation is computed only for sortable types,
which means that the current concept of correlation works as intended
only for B-tree indexes.

Ideally, correlation should be computed for (attribute, index) pair,
taking into account order of values returned by the index scan. Less
ideal but more easier approach can be to ignore the computed correlation
for any index access except B-tree, and just assume some predefined
constant.