select sku_id from shop_feature_brand_sku where feature_value_code = 'xiaomi' intersect select sku_id from shop_feature_cores_sku where feature_value_code = 2 intersect select sku_id from shop_feature_ram_sku where feature_value_code = 4096 Как можно оптимизировать этот запрос? индексы вешать я так понимаю не вариант, cardinality будет низкий. EXPLAIN: http://tatiyants.com/pev/#/plans/plan_1533906548722

Это EAV, сознайтесь? ;) Кстати, похоже, этот сайт показывает планы только Вам, попробуйте лучше https://explain.depesz.com/

это сам подзапрос, потом нужно результаты сджойнить с основной таблицей, с ещё одной таблицей, и отсортировать по цене.

Так покажите полный запрос. Оптимизация "по кускам" нередко имеет мало отношения к делу. И покажите план и \d таблиц(ы).

Это EAV, сознайтесь? ;) Кстати, похоже, этот сайт показывает планы только Вам, попробуйте лучше https://explain.depesz.com/

EAV, вы ж мне такую структуру и предлагали)

https://explain.depesz.com/s/IMPw

EAV, вы ж мне такую структуру и предлагали)

Я никогда никому EAV не предлагал, не надо тут этого. ;)

CREATE TABLE shop_product ( id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), url TEXT NOT NULL UNIQUE, -- TODO: add name (Brand + Model) is_published BOOLEAN NOT NULL DEFAULT 'F', CONSTRAINT url_too_big CHECK(char_length(url) < 1024) ); CREATE TABLE shop_product_sku ( id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), product_id uuid NOT NULL REFERENCES shop_product(id), is_available BOOLEAN NOT NULL DEFAULT 'T', ean13 EAN13 UNIQUE, features JSONB NOT NULL DEFAULT '{}', created_at TIMESTAMP NOT NULL DEFAULT now() ); CREATE TABLE shop_product_sku_offer ( sku_id uuid REFERENCES shop_product_sku(id), seller_id uuid REFERENCES shop_seller(id), price NUMERIC(16,4) NOT NULL, currency_code TEXT NOT NULL REFERENCES currency(code), PRIMARY KEY(sku_id, seller_id), CONSTRAINT positive_price CHECK (price >= 0) ); CREATE TABLE shop_feature ( code TEXT PRIMARY KEY, sort INT NOT NULL DEFAULT 0, is_multiple BOOLEAN NOT NULL DEFAULT 'F' ); CREATE TABLE shop_feature_brand ( code TEXT PRIMARY KEY, sort INT NOT NULL DEFAULT 0 ); CREATE TABLE shop_feature_brand_sku ( sku_id uuid REFERENCES shop_product_sku(id), feature_value_code TEXT REFERENCES shop_feature_brand(code), PRIMARY KEY (sku_id) ); CREATE TABLE shop_feature_ram ( code INT PRIMARY KEY, sort INT NOT NULL DEFAULT 0 ); CREATE TABLE shop_feature_ram_sku ( sku_id uuid REFERENCES shop_product_sku(id), feature_value_code INT REFERENCES shop_feature_ram(code), PRIMARY KEY (sku_id, feature_value_code) ); CREATE TABLE shop_feature_cores ( code SMALLINT PRIMARY KEY, sort INT NOT NULL DEFAULT 0 ); CREATE TABLE shop_feature_cores_sku ( sku_id uuid REFERENCES shop_product_sku(id), feature_value_code SMALLINT REFERENCES shop_feature_cores(code), PRIMARY KEY (sku_id, feature_value_code) );

полного запроса пока нет, я в процессе написания, там ещё есть категории, и нужно выбирать из категорий

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

CREATE TABLE shop_product ( id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), url TEXT NOT NULL UNIQUE, -- TODO: add name (Brand + Model) is_published BOOLEAN NOT NULL DEFAULT 'F', CONSTRAINT url_too_big CHECK(char_length(url) < 1024) ); CREATE TABLE shop_product_sku ( id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), product_id uuid NOT NULL REFERENCES shop_product(id), is_available BOOLEAN NOT NULL DEFAULT 'T', ean13 EAN13 UNIQUE, features JSONB NOT NULL DEFAULT '{}', created_at TIMESTAMP NOT NULL DEFAULT now() ); CREATE TABLE shop_product_sku_offer ( sku_id uuid REFERENCES shop_product_sku(id), seller_id uuid REFERENCES shop_seller(id), price NUMERIC(16,4) NOT NULL, currency_code TEXT NOT NULL REFERENCES currency(code), PRIMARY KEY(sku_id, seller_id), CONSTRAINT positive_price CHECK (price >= 0) ); CREATE TABLE shop_feature ( code TEXT PRIMARY KEY, sort INT NOT NULL DEFAULT 0, is_multiple BOOLEAN NOT NULL DEFAULT 'F' ); CREATE TABLE shop_feature_brand ( code TEXT PRIMARY KEY, sort INT NOT NULL DEFAULT 0 ); CREATE TABLE shop_feature_brand_sku ( sku_id uuid REFERENCES shop_product_sku(id), feature_value_code TEXT REFERENCES shop_feature_brand(code), PRIMARY KEY (sku_id) ); CREATE TABLE shop_feature_ram ( code INT PRIMARY KEY, sort INT NOT NULL DEFAULT 0 ); CREATE TABLE shop_feature_ram_sku ( sku_id uuid REFERENCES shop_product_sku(id), feature_value_code INT REFERENCES shop_feature_ram(code), PRIMARY KEY (sku_id, feature_value_code) ); CREATE TABLE shop_feature_cores ( code SMALLINT PRIMARY KEY, sort INT NOT NULL DEFAULT 0 ); CREATE TABLE shop_feature_cores_sku ( sku_id uuid REFERENCES shop_product_sku(id), feature_value_code SMALLINT REFERENCES shop_feature_cores(code), PRIMARY KEY (sku_id, feature_value_code) );

Это не \d. Так не видно ни индексов, ни FK. Ну или схему целиком (pg_dump) покажите...

там очень много таблиц под локализации и прочее, как бы так выбрать их нужные. сейчас скину. Вот кстати на эти отдельные таблицы эксплейны с индексами и без: Без индексов: https://explain.depesz.com/s/IMPw С индексами: https://explain.depesz.com/s/GUxw

https://pastebin.com/rrpFbrRP

там на некоторых таблицах по паре индексов, забыл дропнуть

там на некоторых таблицах по паре индексов, забыл дропнуть

Охо-хо. ;) Поясните схему? . shop_product_sku — это что? . "shop_feature_ram_sku_pkey" PRIMARY KEY, btree (sku_id, feature_value_code) — это почему так?

И ещё, какая цель запроса (вдруг Вы не тот/ неправильный написали, а мы с ним будем сидеть)?

shop_product_sku - артикулы товара shop_feature_ram_sku_pkey - первичный ключ из двух стобцов, хотя тут pkey на sku_id надо вешать, т.к. на 1 sku_id - только одно значение может быть. цель запроса, выбрать все артикулы из таблицы с брендами (xiaomi) с количеством ядер (2) и памятью (4гб)

shop_product_sku - артикулы товара shop_feature_ram_sku_pkey - первичный ключ из двух стобцов, хотя тут pkey на sku_id надо вешать, т.к. на 1 sku_id - только одно значение может быть. цель запроса, выбрать все артикулы из таблицы с брендами (xiaomi) с количеством ядер (2) и памятью (4гб)

> хотя тут pkey на sku_id надо вешать Тогда да. > цель запроса, выбрать все артикулы из таблицы с брендами (xiaomi) с количеством ядер (2) и памятью (4гб) Т.е. SELECT ... FROM shop_product_sku AS sps WHERE EXISTS (SELECT 1 FROM shop_feature_brand_sku AS sfb WHERE sfb.sku_id = sps.id AND sfs.feature_value_code = 'xiaomi') AND EXISTS (SELECT 1 FROM ... AS sfx WHERE sfx.sku_id = sps.id AND sfx.feature_value_code = ...) AND EXISTS (SELECT 1 FROM ... AS sfx WHERE sfx.sku_id = sps.id AND sfx.feature_value_code = ...) Т.е. со всеми свойствами?

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

т.е. в результирующую таблицу не обязательно их выводить, свойства эти.

сейчас попробую ваш запрос

сейчас попробую ваш запрос

Так Вы сначала исправьте индексы, сделайте VACUUM ANALYZE;, а потом можно смотреть...

shop_product_sku - артикулы товара shop_feature_ram_sku_pkey - первичный ключ из двух стобцов, хотя тут pkey на sku_id надо вешать, т.к. на 1 sku_id - только одно значение может быть. цель запроса, выбрать все артикулы из таблицы с брендами (xiaomi) с количеством ядер (2) и памятью (4гб)

А почему uuid-ы, кстати?

ваш запрос работает чуть быстрее, около 14 мс на полную выборку, с лимитом очень быстро.

uuid, хз. со стороны приложения удобней с ними работать.

батчами вставки делать.

т.е. товар -> куча артикулов -> у каждого артикула куча свойств.

и не нужно returning id

но вообще я эксперементирую, не ищите тут каких-то праткических опытных решений)) я с postgresql пока играюсь, щупаю функционал и выбираю примерный дизайн базы под проект свой.

uuid, хз. со стороны приложения удобней с ними работать.

Хорошо, только за это удобство Вам придётся заплатить производительностью, и, возможно, очень дорого. Какое у Вас там железо (SSD? RAM?) и размер базы?

ваш запрос работает чуть быстрее, около 14 мс на полную выборку, с лимитом очень быстро.

Вы уже все советы выполнили? Если да, покажите же план. ;)

https://explain.depesz.com/s/wSQr это с джойном с табличкой офферов и сортировкой по цене

я делал vacuum + индекс один прибил

но вроде больше ненужных индексов нет.

пк можно поменять конечно, но это не всегда так будет что на один артикул - одна характеристика.

размера базы нет пока, приблизительно около 1 млн. товаров, и артикулов около 3-4 млн. будет

вот обычный запрос без джойна и без ордера: https://explain.depesz.com/s/lNMh

размера базы нет пока, приблизительно около 1 млн. товаров, и артикулов около 3-4 млн. будет

Ну так можно же нагенерировать тестовых данных да просто посмотреть, что там получится.

ну вот пока нагенерировал 69k артикулов. это мало, конечно. сейчас это все в VirtualBox крутится, 4 гб, и 2 ядра.

вот обычный запрос без джойна и без ордера: https://explain.depesz.com/s/lNMh

Можно и дальше улучшать, кстати. А по железу будет что? Да, и какая версия PostgreSQL?

10.4 вроде

да, 10.4

по железу, будем смотреть исходя из требований и роста проекта. пока будем сидеть на обычной VPS на вультр. и по мере роста проекта апгрейдить план. когда упремся во что-то серьезное, будем уже смотреть :)

ну вот пока нагенерировал 69k артикулов. это мало, конечно. сейчас это все в VirtualBox крутится, 4 гб, и 2 ядра.

Лучше, конечно, нагенерировать примерно расчётный объём. Т.к. интересно отношение размера базы к RAM.

ну я вот поставил сейчас генерировать товары.

по железу, будем смотреть исходя из требований и роста проекта. пока будем сидеть на обычной VPS на вультр. и по мере роста проекта апгрейдить план. когда упремся во что-то серьезное, будем уже смотреть :)

Т.е. это 4 GB RAM, а диск? И настройки PostgreSQL какие?

А почему uuid-ы, кстати?

ееее, снова uuid -дискуссия

диск 850 ево, настройки взял с сайта leopard, вбил туда 4гб, 2 ядра.

если прям интересно, могу скопировать, сек

ееее, снова uuid -дискуссия

А что, было уже? А какие были аргументы "за"? ;)

# Add settings for extensions here # DB Version: 10 # OS Type: linux # DB Type: web # Total Memory (RAM): 4 GB # CPUs num: 2 # Data Storage: ssd max_connections = 200 shared_buffers = 1GB effective_cache_size = 3GB maintenance_work_mem = 256MB checkpoint_completion_target = 0.7 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 5242kB min_wal_size = 1GB max_wal_size = 2GB max_worker_processes = 2 max_parallel_workers_per_gather = 1 max_parallel_workers = 2

>>max_connections = 200

серьезно?

ребятки, это обычный конфиг не заточенный ни под что. https://pgtune.leopard.in.ua/#/

# Add settings for extensions here # DB Version: 10 # OS Type: linux # DB Type: web # Total Memory (RAM): 4 GB # CPUs num: 2 # Data Storage: ssd max_connections = 200 shared_buffers = 1GB effective_cache_size = 3GB maintenance_work_mem = 256MB checkpoint_completion_target = 0.7 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 5242kB min_wal_size = 1GB max_wal_size = 2GB max_worker_processes = 2 max_parallel_workers_per_gather = 1 max_parallel_workers = 2

Многовато connections, нет? Вам виднее, конечно... И ещё, можно поднять cpu_tuple_cost до 0.1 (или даже 0.2). Зависит, опять-таки, от размера базы.

если это будет олтп, то без внешнего пулера не взлетит

или пулеров

у меня в приложении есть пулл коннектов

у меня в приложении есть пулл коннектов

Ну так снижайте до "реального" кол-ва одновременных активных соединений.

ну я вот поставил сейчас генерировать товары.

Ладно, тогда, как нагенерируете (и измените настройки), отпишитесь, хорошо?

А что, было уже? А какие были аргументы "за"? ;)

а есть серьезные против, кроме байтокопания? )

Ладно, тогда, как нагенерируете (и измените настройки), отпишитесь, хорошо?

окей.

а есть серьезные против, кроме байтокопания? )

А то. :) Смотря в каком качестве, конечно. Как первичные ключи? Во всех таблицах?

а еще, у вас настройки по сборке мусора остались дефолтные

а еще, у вас настройки по сборке мусора остались дефолтные

Ну, для тестов это не так важно...

а есть серьезные против, кроме байтокопания? )

И какие, всё же, за?

плохо, когда вы тестируете одно, с мыслями о другом)

И какие, всё же, за?

сборка мусора же на апдейты делиты нужна только, или не?

ну и поддержу

ERROR: S client not available

id uuid

как-то не очень)

боитесь светить ID - шифруйте на уровне API

хотя внутренние ключи крайне редко нужно пробрасывать дальше

И какие, всё же, за?

нет единой точки синхронизации (я про автоинкремент, его в распределенку переносить - сплошная боль), очень легко переносится в другие хранилища, в редких случаях необходимости позволяет идентифицировать одну сущность вне зависимости от того, в какой таблице она валяется

прятать id это как мини приятный бонус. идея не в этом.

зачем прятать айди (и тем более шифровать его)

интернсно

В кратце. Перед установкой patroni, ставим на выбор: zookeeper,etcd, consul. Потом разворачиваем сам patroni. Цепляем к pgbouncer к pgsql, чтоб не жрать коннекты Поверх накатываем haproxy. А вот тут самое интересное. Хапрокси настраиваем на pgbouncer на всех хостах, где лежит pgsql. Нюанс в том что у хапрокси 2 бэкенда один - это полноценный, он же мастер второй - это readonly(standby) Механизм переключения таков: Патрони имеет на 8008м порту вебсервер, который отвечает 200 если мастер и 503 если standby по этому тюним haproxy примерно так: frontend ft_cluster-pgsql bind *:5432 name cluster-pgsql default_backend bk_cluster-pgsql backend bk_cluster-pgsql option httpchk balance roundrobin http-check expect status 200 # Return 200 = Master state, Return 503 = Slave default-server inter 3s fall 3 rise 2 server pgsql-01 db01.local:6432 maxconn 0 check port 8008 server pgsql-02 db02.local:6432 maxconn 0 check port 8008 server pgsql-03 db03.local:6432 maxconn 0 check port 8008 frontend ft_cluster-pgsql bind *:5433 name cluster-pgsql default_backend bk_cluster-pgsql backend bk_cluster-pgsql option httpchk balance roundrobin http-check expect status 503 # Return 200 = Master state, Return 503 = Slave default-server inter 3s fall 3 rise 2 server pgsql-01 db01.local:6432 maxconn 0 check port 8008 server pgsql-02 db02.local:6432 maxconn 0 check port 8008 server pgsql-03 db03.local:6432 maxconn 0 check port 8008

В итоге получаем haproxy на каждом pgsql сервере, на 5432 - rw порт, 5433 - ro порт

=)

@amavlyanov

и да, переключение мастера и перезаливка реплики через патрони проходит гораздно легче

нет единой точки синхронизации (я про автоинкремент, его в распределенку переносить - сплошная боль), очень легко переносится в другие хранилища, в редких случаях необходимости позволяет идентифицировать одну сущность вне зависимости от того, в какой таблице она валяется

> нет единой точки синхронизации (я про автоинкремент, его в распределенку переносить - сплошная боль А в чём минус точки-то? Это, как-раз, попытки микрооптимизации, IMHO. А если нужно какие-то данные переносить в распределёнку, можно добавить uuid конкретно к ним. Потом, если будет нужно. > очень легко переносится в другие хранилища, Смотря чего, наверное. > в редких случаях необходимости позволяет идентифицировать одну сущность вне зависимости от того, в какой таблице она валяется Это можно и с обычным sequencе сделать (кстати, что удивительно, видел базы, где делали, и не так уж мало).

зачем прятать айди (и тем более шифровать его)

А вдруг клиент узнает, что он у Вас не первый. ;)

А для нераспределённой базы какие плюсы UUID-ов?

> нет единой точки синхронизации (я про автоинкремент, его в распределенку переносить - сплошная боль А в чём минус точки-то? Это, как-раз, попытки микрооптимизации, IMHO. А если нужно какие-то данные переносить в распределёнку, можно добавить uuid конкретно к ним. Потом, если будет нужно. > очень легко переносится в другие хранилища, Смотря чего, наверное. > в редких случаях необходимости позволяет идентифицировать одну сущность вне зависимости от того, в какой таблице она валяется Это можно и с обычным sequencе сделать (кстати, что удивительно, видел базы, где делали, и не так уж мало).

> А в чём минус точки-то ну встречный вопрос в чем минус уидов ) просто от этого надо по возможности избавляться > А если нужно какие-то данные переносить в распределёнку, можно добавить uuid конкретно к ним. Потом, если будет нужно. Как правило, шагнуть обратно уже нельзя. Всякие там сео и прочее.

юзаю UUID-ы, когда нужно сгенерировать строку-ключ для уникального, неподбираемого диплинка

соответственно, на поле с таким ключом нужен индекс

> А в чём минус точки-то ну встречный вопрос в чем минус уидов ) просто от этого надо по возможности избавляться > А если нужно какие-то данные переносить в распределёнку, можно добавить uuid конкретно к ним. Потом, если будет нужно. Как правило, шагнуть обратно уже нельзя. Всякие там сео и прочее.

> просто от этого надо по возможности избавляться Почему?

потому что все, что не синхронизируется друг с другом, легко работает в параллель по определению?

1. нагрузка на построение\перестроение индексов выше

2. деревья дефрагментированые

3. соответсенно, доступ по ключу медленнее

ну и оверхед по занимаемому месту

как в таблице, так и в индексах

если вам для работы не хватает обычного serial, то вы делаете что-то не так

ну и еще раз

если вам для работы не хватает обычного serial, то вы делаете что-то не так

640к оперативы!

если у вас ключи, которые нужны для целостности модели данных, доступны для отображения выше апп-уровня (фронт) - вам стоит еще раз переосмыслить, что вы и зачем делаете

> А в чём минус точки-то ну встречный вопрос в чем минус уидов ) просто от этого надо по возможности избавляться > А если нужно какие-то данные переносить в распределёнку, можно добавить uuid конкретно к ним. Потом, если будет нужно. Как правило, шагнуть обратно уже нельзя. Всякие там сео и прочее.

> ну встречный вопрос в чем минус уидов ) 1. Раз это первичные ключи (которые могут быть кодами товаров, номерами заказов и т.п.), не стоит недооценивать их неудобство для людей. Всего одна попытка назвать такой по телефону (и т.п.), и наступает просветление. :) Ну вот, пока писал, меня уже опередили с многими другими причинами. ;)

если вам для работы не хватает обычного serial, то вы делаете что-то не так

Да ладно, bigserial тоже бывает нужен.

3. соответсенно, доступ по ключу медленнее

И они "лезут" во все FK и JOIN-ы (как мы тут только что видели).