Re: BRIN cost estimate breaks geometric indexes

From: Egor Rogov <e(dot)rogov(at)postgrespro(dot)ru>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: BRIN cost estimate breaks geometric indexes
Date: 2020-01-20 23:07:17
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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
> 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:
> Debugging session on #postgis IRC channel leads to this ticket to
> create a (not that meaningful) correlation statistics for geometry
> datatype:
> Postgres Professional mentioned symptoms of the issue in their
> in-depth manual:
> - box datatype
> showed same unusable BRIN symptoms for them.

(Translated to English:

> 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

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Isaac Morland 2020-01-20 23:11:18 Re: Decade indication
Previous Message Justin Pryzby 2020-01-20 21:49:29 Re: error context for vacuum to include block number