Hi, Patch may look as simple as this one: https://patch-diff.githubusercontent.com/raw/postgres/postgres/pull/49.diff
Previous mention in -hackers is available at https://postgrespro.com/list/id/cakjs1f9n-wapop5xz1dtgdpdqmzegqqk4sv2mk-zzugfc14...@mail.gmail.com - seems everyone overlooked that patch there breaks geometric indexing back then. On Tue, Jan 21, 2020 at 2:07 AM Egor Rogov <e.ro...@postgrespro.ru> wrote: > 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. > > > > > -- Darafei Praliaskouski Support me: http://patreon.com/komzpa
