Postgres Pro
Downloads
Home   >   mailing lists

BRIN cost estimate breaks geometric indexes - Mailing list pgsql-hackers

From Darafei "Komяpa" Praliaskouski
Subject BRIN cost estimate breaks geometric indexes
Date
Msg-id CAC8Q8tKXFhHny5KEGcsOZBGUQ7kggfjn7c3ZG47U2VU6ThkBbg@mail.gmail.com
Whole thread Raw
Responses Re: BRIN cost estimate breaks geometric indexes  (Egor Rogov <e.rogov@postgrespro.ru>)
List pgsql-hackers
Tree view
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.

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.

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?

--
Darafei Praliaskouski

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Increase psql's password buffer size
Next
From: Christoph Berg
Date:
Subject: Re: libxml2 is dropping xml2-config