BRIN cost estimate breaks geometric indexes
| От | Darafei "Komяpa" Praliaskouski |
|---|---|
| Тема | BRIN cost estimate breaks geometric indexes |
| Дата | |
| Msg-id | CAC8Q8tKXFhHny5KEGcsOZBGUQ7kggfjn7c3ZG47U2VU6ThkBbg@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: BRIN cost estimate breaks geometric indexes
|
| Список | pgsql-hackers |
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.
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.
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
Support me: http://patreon.com/komzpa
В списке pgsql-hackers по дате отправления: