Индексы в PostgreSQL — 6

[JeStoneDev]

Идея состоит в рекурсивном разбиении области на четыре части (квадранта) по отношению к центральной точке.

Как происходит процесс вычисления центральной точки в дереве квадрантов? Медианы по обеим осям? Половина разности максимального и минимального значений x и y? Или какой-то более сложный алгоритм? Как вычисляется центральная точка, если построить индекс по пустой таблице? Может ли получиться, что после заполнения таблицы, в которой уже присутствует sp-gist индекс, все точки будут в одном квадранте верхнего уровня, а 3 других будут пустыми?

[erogov]

Используются средние значения по осям. Забавно, что есть код и для медианы, но чтобы его включить, надо пересобрать Постгрес с #define USE_MEDIAN. Если интересно, загляните в src/backend/access/spgist/spgistquadtreeproc.c.

Центроид вычисляется, когда в дерево добавляется новый внутренний узел. Так что имеющиеся точки, которых уходят в новый узел, распределяются по квадрантам условно равномерно. Так что такой ситуации возникнуть не должно.

[SXN]

Доброго времени суток, хотел спросить как заменить в запросе IN и NOT IN там проста данные много (около миллиона) и будет расти. Поля индексированное но все равно запросы работают дольше и дольше
Заранее спасибо!

[erogov]

Хм, и какие выводы можно делать, не видя хотя бы EXPLAIN ANALYZE запроса?
Из самых общих соображений могу сказать, что если данных много, то индексы будут скорее мешать, чем помогать. Возможно, вам надо добиться такого плана, чтобы таблицы соединялись с помощью HASH JOIN.
Но это все лечение по фотографии. Покажите план запроса, тогда и подумаем. Только в личку, потому что SP-GiST тут явно ни при чем.

[Komzpa]

А что нужно, чтобы Postgres научился для SP-GiST делать compress и recheck, такой же, как и для GiST? Без этого его поддержку никак не могут запилить в PostGIS — он же не может сохранить в себе геометрию длиннее 8 килобайт, что в общем-то частое явление.

[erogov]

Ну, общий алгоритм состоит в том, чтобы капать на мозги нашим разработчикам (:
Но я что-то не могу сообразить, как использовать SP-GiST для произвольной хитроизогнутой геометрии. Как эти фигуры непересекающимся образом развести по квадрантам?

[Komzpa]

GiST делает compress из фигуры в её box, а потом box можно паковать уже как многомерную точку. На этапе выборки — быстро достать бокс, а потом сделать recheck по полной геометрии.

[erogov]

В принципе да, на мой взгляд выглядит вполне себе реализуемо. Будем капать.

[Komzpa]

Я вам даже ссылками помогу:
trac.osgeo.org/postgis/ticket/1847
github.com/pramsey/postgres/tree/spgistcompress
github.com/postgis/postgis/compare/svn-trunk...mohitkharb:spgist
github.com/postgis/postgis/compare/svn-trunk...pramsey:spgist

Это дело начинали на Google Summer of Code в 2014, но задача повисла, так как больше на стороне Postgres, чем на стороне PostGIS.

[smagen]

Recheck в SP-GiST уже есть и кажется всегда был. См. структуру данных, которую возвращает leaf_consistent.

typedef struct spgLeafConsistentOut
{
	Datum		leafValue;		/* reconstructed original data, if any */
	bool		recheck;		/* set true if operator must be rechecked */
} spgLeafConsistentOut;

Патч о добавлении метода compress был на commitfest, но он как-то заглох. Будем его возобновлять.
www.postgresql.org/message-id/flat/[e-mail]#[e-mail]
Насколько я понял отсюда, пока реализован opclass только для точек. Точки имеют ограниченный размер, поэтому для них compress не так чтобы прямо жизненно необходим. Но, конечно, с compress'ом намного лучше, мы будем им заниматься.

[Komzpa]

Проблема в том, что в PostGIS не отдельного типа для точки — они все по сути своей geometry. В рамках GSoC сделали скоростную кластеризацию для точек, тикет про неё — это уже навороты.

Опкласс для произвольной геометрии, похоже, есть у Пола в github.com/postgis/postgis/compare/svn-trunk...pramsey:spgist.

[smagen]

Да, но насколько я понял, производительность у этого opclass'а не очень, если верить самому Полу.
www.postgresql.org/message-id/CACowWR2LhVi4JHEVV=[e-mail]
Скорее стоит смотеть в сторону отображения 2-мерное прямоугольника в 4-мерную точку, по аналогии с acdf2a8b372aec1da09370fca77ff7dccac7646d.

[vvmtutby]

Вопрос несколько «приземлённый», зато важный на практике:
Postgres поддерживает VSS ( API позволяющее получить консистентность / целостность резервных копий)?

[erogov]

Это который микрософтовский Volume Shadow? Нет, его не поддерживает, конечно.

Но, разумеется, есть собственные средства, которые позволяют делать горячие резервные копии. Все это описано в документации в разделе Создание базовой резервной копии через низкоуровневый API.

[vvmtutby]

Хорошо, переходим к примерам:
делаем бэкап VM «целиком»
Если внутри виртуальной машины OS Windows + MS SQL ( или Oracle), то резервная копия гарантирована целостна

(
Для конкретики возьмём Hyper-V, Windows внутри VM для «честного» VSS,
а не для псевдо-VSS сброса буферов файловой системы на диск, как в варианте H-V VM с Linux + СУБД

просьба учитывать, что вопрос не про плюсы/минусы ESX/H-V и(или) *nix/Win и(или) т.п.
)

Если внутри Postgres, то?...

[erogov]

Я не очень представляю себе, как выполняется "бэкап VM целиком". Если для этого используется механизм снимков (snapshot) Hyper-V, то да, резервная копия должна быть годной (файлы данных будут рассогласованны, но в копии будут все необходимые журнальные файлы, чтобы Постгрес при старте автоматически восстановил целостность).

P. S. Казалось бы, при чем тут индексы?

[vvmtutby]

> P.S. Казалось бы, при чем тут индексы?
Намёк понял Ж-)

[vvmtutby]

-

[SanSYS]

А что скажете про поиск по тексту в сравнении с BTREE при использовании text_pattern_ops?

CREATE INDEX idx_companies_btree_text_pattern_ops
  ON public.companies
  USING btree
  (ogrn text_pattern_ops, id);

Вполне отлично работает, поддерживает like/ilike запросы не только с конца, но и с начала, если выборка идёт только по колонкам, включенным в индекс

[smagen]

SP-GiST по тексту может быть существенно компактнее, чем B-tree в случае если у строк много одинаковых префиксов (к примеру, если URL'ы хранятся).

[JDTamerlan]

Подскажите пожалуйста, а почему can_unique не поддерживается для SP-GiST?

[erogov]

Думаю, потому, что дополнительная сложность перевешивает потенциальную пользу. Уникальность поддерживается только B-деревом, и там для этого много наворочено. А кому нужны первичные ключи по точкам?

Но если прям надо, то можно с помощью EXCLUDE сымитировать уникальность. Что-нибудь типа

EXCLUDE USING spgist(p WITH ~=)

[JDTamerlan]

понял

"А кому нужны первичные ключи по точкам?"
в данном случае лично я бы хотел иметь can_unique для (radix tree)

Спасибо Вам! :-)