Меня продолжает удивлять PostgreSQL. Невероятная СУБД с широким спектром возможностей.
Помимо документации, считаю лучшей книгой по углубленному изучению и пониманию этого продукта "PostgreSQL изнутри" Егора Рогова. Книга бесплатная для скачивания и недавно была выпущена её обновленная версия для 15 - последней стабильной - версии PostgreSQL.
Тут стоит сделать небольшую рекламу их YouTube-канала. Вы без труда можете найти его по имени "Postgres Professionals". На канале есть ряд плейлистов для углубленного изучения этой СУДБ. Авторы материалов - это активные контрибьюторы PostgreSQL. Скорее всего доклады Олега Бартунова вы слышали не раз.
Сейчас читаю в этой книге про внутреннюю организацию B-Tree индексов и их реализацию в PostgreSQL. Напомню, что PostgreSQL был создан как расширяемый продукт, поэтому вы можете достаточно просто создать свой тип, определить как для этого типа работают операторы и создать класс таких операторов, которого будет достаточно, чтобы работала сортировка в B-Tree индексе:
CREATE TYPE capacity AS (amount integer, unit capacity_units);
CREATE OPERATOR #># (LEFTARG = capacity, RIGHTARG = capacity, FUNCTION = capacity_gt);
CREATE OPERATOR CLASS capacity_ops
DEFAULT FOR TYPE capacity
USING btree AS
OPERATOR 1 #<#,
OPERATOR 2 #<=#,
OPERATOR 3 #=#,
OPERATOR 4 #>=#,
OPERATOR 5 #>#,
FUNCTION 1 capacity_cmp(capacity,capacity);
CREATE INDEX ON test(cap);
Из полезного, на чем стоит отдельно заострить внимание - это эффективное использование составных индексов (в официальной документации Multicolumn Index). Составной индекс - индекс составленный из конкатенации нескольких колонок таблицы. Эффективное использование индекса возможно с WHERE по всем колонками такого индекса, либо по первой, либо по первой и второй, либо по первой, второй и третьей и т.д. Тут очень важен порядок! Т.е. если индекс составлен из колонок company_id и contact_id, то попытка выборки только по contact_id (вторая колонка в индексе) вынудит базу данных игнорировать вторую колонку.
Почему? Потому что сортировка в индексе выполнена по конкатенации company_id и contact_id. Если бы contact_id был объявлен на первом месте при создании индекса, то выборка просиходила бы по B-Tree индексу.
Чтобы проще запомнить как это работает, представьте телефонный справочник. В первую очредеь он упорядочен по фамилии, а искать по имени используя оглавление попросту невозможно.
Понимая структуры данных, используемые в B-Tree индексе можно его эффективнее использовать. Например, если вы часто выполняете поиск по вхождению подстроки и нужно выполнить поиск по соответсвию окончания, то лучше отзеркалировать строку, чтобы выполнять поиск вхождения по началу с помощью оператора LIKE.
Из интересного: