Друзья такой вопрос Есть в таблице 2 поля: id и el_id.

id - первичный ключ, а по el_id создал индекс
Я делаю запрос Х по типу SELECT el_id FROM table ORDER BY el_id DESC; Делается он достаточно долго.
Данные сейчас лежат так: непоследовательно идет id, а el_id вперемешку
Сейчас в таблице 150 241 790 строк.

Как увеличить скорость?
В таблицу планируется, что буду класть данные так, что поле el_id будет последовательно увеличиваться (то есть не будет вперемешку)
Если я сделаю копию этой таблицы, где я уберу оригальное полe id , а поле el_id сделаю первичным ключом, и сложу так, что el_id будет увеличиваться от минимального к максимальному, то

psql (PostgreSQL) 11.9 (Debian 11.9-0+deb10u1)
запрос: https://pastebin.com/kaV0wLkw

https://t.me/pgsql/303899

Даниил Агниашвили

https://t.me/pgsql/303899 Текстом покажите, естественно.

https://pastebin.com/kaV0wLkw вроде всё

Нет, не всё, explain очевидно обрезан.

точно, не увидел, обновил,

в равке есть

И из 4 запрошенных пунктов Вам "удался" только один. Видимо, помощь Вам всё-таки не нужна.

Кстатида, Ярослав, кроме прочего, тонко намекает что вы ещё и buffers в explain забыли.

Это я просто подумал "почему один, когда вроде два?"

https://pastebin.com/XMQQEPE7 Хм, всё полностью выложил сюды

Вообще, с планом проблема, конечно. То есть я бы, представляя предметную область -- сначала хотел бы чтобы выбрали все houses по указанным id_addresses (их там в пределах пары десятков тысяч будет), а потом ужэ по id_house сканировало... А тут он явно наоборот ищет, неудивительно с такой селективностью что пол-таблицы cache_table по индэксу переворачивает. Ну и, из очевидного -- моя идея плохо сработает если нет индэкса на cache_table(house_id, index_id). Так что можно попробовать его создать (вместе c analyze), и посмотреть, пересмотрит ли Postgres своё поведение.

Наконец-то, спасибо! ;) Далее, вот Вы тут https://t.me/pgsql/308470 писали: > Данные сейчас лежат так: непоследовательно идет id, а el_id вперемешку > Сейчас в таблице 150 241 790 строк. > В таблицу планируется, что буду класть данные так, что поле el_id будет последовательно увеличиваться (то есть не будет вперемешку) Про какую именно таблицу была речь? cache_table или houses? Что сейчас видно — это то, что Вы "попали" на т.н. early abort plan — нужно посмотреть, что дают альтернативы, и для этого нужно больше знать про эти данные (см. хотя бы вопросы коллег выше).

Имею ввиду про таблицу cache_table

Понял

И да, а где Foreign Key cache_table(id_house) REFERENCES houses? Не выложили или его нет?

Его нет

А почему (это может влиять на оценки, например)? Тем не менее — давайте больше информации. ;)

Я вот это имею в виду, например: > сначала хотел бы чтобы выбрали все houses по указанным id_addresses (их там в пределах пары десятков тысяч будет) Это действительно так? Ну и вообще, сколько rows в таблице houses, сколько отбирается отдельно по условиям (без LIMIT) из неё и всего из cache_table... особенно, какие оценки этого всего. Т.е., например: EXPLAIN (ANALYZE, BUFFERS) SELECT id_addresses FROM houses WHERE t2.id_addresses IN (...); Ну и так далее.

Еще больше информации. Тут инфа о кейсе, о полях и последних запросах которые я делал, https://docs.google.com/document/d/1ahpmWn1-QZ8a2cD54prkpH0ixpJP78F1BiErvMPztnQ/edit?usp=sharing

Читаю... > сделал vacuum full houses, cache_table А сделайте пока VACUUM ANALYZE houses, cache_table; — оценки rows какие-то плохие, лучше начать с этого.

Хорошо

Ну и вот это (например): -> Index Scan using houses_index on houses t2 (cost=0.56..909020.31 rows=1242288 width=4) (actual time=0.915..255.822 rows=71594 loops=3) Index Cond: (id_addresses = ANY ('{1300337,ПЕРЕЧИСЛЕНИЕ,17572575}'::integer[])) Buffers: shared hit=60662 read=12015 как бы намекает нам (конкретно, наличие shared read при таком объёме считанного), что на этом сервере плохо либо с "железом", либо с tuning OS/PostgreSQL. Там вообще что-то настраивалось?

Сервер https://ru.hetzner.com/hosting/produkte_rootserver/ax41-nvme/ postgresql.conf: https://pastebin.com/1tVnYLGz pg_ctl.conf пустой https://pastebin.com/ueSBPE4J Мне кажется, что из решения - надо попробовать 1) сделать так, чтобы выборка была учитывая дату, тогда получится существенно уменьшить размер области поиска 2) возможно, переделать эти две таблицы, мб добавить партиции ( по датам опять же или по регионам, мб сделать поле Количество, чтобы делать sum() (?))

ssd из раида сделал страйп на терр

Явно не настраивался, да. Если там работает только PostgreSQL, то: shared_buffers = 128MB # min 128kB на таком железе — это просто смешно. А вот work_mem = 500MB # min 64kB возможно, слишком много. Попробуйте хоть http://pgconfigurator.cybertec.at/ для того, чтобы получить ориентировочные значения параметров. И да, synchronous_commit = off # synchronization level; Возможные потери транзакций не волнуют? ;) include_dir = 'conf.d' # include files ending in '.conf' from И вот тут ничего нет, кстати? Ну и проще / надёжнее с работающего сервера запросить pg_settings, чем в файлах копаться. И да, если уж заниматься tuning, то начинать стоит с базовой настройки OS... вообще, см. http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > Мне кажется, что из решения - надо попробовать А мне кажется, что прежде чем что-то делать, стоит разобраться с проблемой. ;) > возможно, переделать эти две таблицы, мб добавить партиции Чтобы всё стало ещё хуже? Кто Вам сказал, что partitioning — это fairy dust (зачастую оно делает всё только хуже)?

конфиг файл взял с домашнего сервака, который настраивал под себя. понял

Хмм... при чём тут реплика? При таких настройках committed transactions могут быть потеряны при обычном crash / immediate shutdown. Впрочем, им виднее, устраивает это или нет...

В общем, как перенастроите (если есть возможность), и выполнится VACUUM — можно будет продолжить...

Хорошо, возможность есть, позже отпишу

Привет еще раз! Настроил вот конфиг https://pastebin.com/Pq3G3wgK Снова Сделал запрос на EXPLAIN (ANALYZE, BUFFERS) (без LIMIT) Получился такой выхлоп https://pastebin.com/VWf4mCRt док с предыдущими тут https://docs.google.com/document/d/1ahpmWn1-QZ8a2cD54prkpH0ixpJP78F1BiErvMPztnQ/edit?usp=sharing

лишь сам постгрес) сттоит сервер перезапусть?

вакуум фулл на всю БД я не смогу сделать, на сервере меньше 600гб занято данными, 300гб свободно или только фулл тех злосчастных двух таблиц? cache_table + houses

Хмм... а точно настройки применились (опять-таки, я же Вам советовал смотреть в pg_settings — может, в include_dir = 'conf.d' всё, что Вы поставили, "перебивается")? > или только фулл тех злосчастных двух таблиц? cache_table + houses Хмм... а что Вы до этого делали? Так VACUUM ANALYZE после VACUUM FULL был выполнен или нет?

conf.d пустая директория. надо её удалить? сейчас выполнил: daniilak=# vacuum full verbose houses; INFO: vacuuming "public.houses" INFO: "houses": found 0 removable, 49726993 nonremovable row versions in 1056642 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 139.84 s, system: 5.61 s, elapsed: 147.62 s. VACUUM ____ daniilak=# vacuum full verbose cache_table; INFO: vacuuming "public.cache_table" INFO: "cache_table": found 0 removable, 150241790 nonremovable row versions in 956955 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 182.87 s, system: 4.32 s, elapsed: 189.11 s. VACUUM daniilak=# vacuum analyse houses, cache_table; VACUUM

> conf.d пустая директория. надо её удалить? Не надо. Просто откуда нам знать, есть там что-то или нет? ;) daniilak=# vacuum analyse houses, cache_table; VACUUM Это только сейчас было сделано? Если да, тогда интересуют новые планы запросов.

Логично) до этого тоже делал этот запрос. в любом случае заново сделал EXPLAIN (ANALYZE, BUFFERS) https://pastebin.com/fGAaLb2U

Послушайте... немного не в тему, но что-то тут не так, мне кажется (но не факт). Что выдаёт SHOW shared_buffers;? И что происходит при повторении запроса (меняются ли shared hit / read в плане)?

Даниил Агниашвили
SHOW shared_buffers; == 16GB меняются

Значит, просто не закешировалось, ну и ладно. Так, а у: SELECT t1.index_id FROM cache_table AS t1 WHERE EXISTS ( SELECT 1 FROM houses AS t2 WHERE t1.id_house = t2.id AND t2.id_addresses IN (...) ); тот же план, на всякий случай? А, и по tuning — с таким "железом" стоит поставить ещё cpu_tuple_cost = 0.1.

у этого запроса надо еще сделать ORDER BY t1.index_id; выхлоп: два раза запускал https://pastebin.com/Ff65xwQd запустил тот же запрос, но без ORDER BY (для интереса) выхлоп: https://pastebin.com/v3pZtQ4J

> у этого запроса надо еще сделать ORDER BY t1.index_id; А пропустил, извините. > выхлоп: два раза запускал Да, план тот же. А: > по tuning — с таким "железом" стоит поставить ещё cpu_tuple_cost = 0.1. Вы пробовали? Если план от этого не изменится, то уже нужно либо улучшать статистику по houses.id_addresses, либо создавать подходящие индексы.

cpu_tuple_cost = 0.1. - сейчас добавлю, после этого надо делать вакуум фул/анализ?

Это можно сделать прямо в сессии: SET cpu_tuple_cost = 0.1; EXPLAIN (ANALYZE, BUFFERS) SELECT ... > после этого надо делать вакуум фул/анализ? Нет. И вообще, не выполняйте VACUUM FULL "просто так" — это делается только в "крайних" случаях.

Хм, а чем грозит выполнение просто так?

Сделал: https://pastebin.com/eFYmA9u6

Блокированием всех запросов, которые обращаются к этой таблице; потерей возможностей index-only scans и HOT updates; отставанием реплик; да и просто напрасной нагрузкой.

Т.е. остаётся либо статистика: ALTER TABLE houses ALTER COLUMN id_addresses SET STATISTICS 10000; VACUUM ANALYZE houses; либо индексы, в "худшем" случае: CREATE INDEX ON houses(id_addresses, id); CREATE INDEX ON cache_table(id_house, index_id);

cache_table(id_house, index_id) индекс уже есть, а SET STATISTICS - где можно подробнеее от об этом почтитать

В том, что Вы приводили, этого индекса не было. А прочитать можно в https://www.postgresql.org/docs/current/planner-stats.html#id-1.5.13.5.3 и в https://www.postgresql.org/docs/current/sql-altertable.html , например.

Только скорее cache_table(index_id, id_house).

Хмм... зачем?

У него там limit 100 по этому index_id, такое количество результатов скорее всего найдётся довольно скоро, притом примерно index_only scanом. А при выборке по id_house -- ему придётся сначала все выбрать, потом отсортировать.

А такой вариант уже был, вот он: Limit (cost=1001.16..14871.56 rows=100 width=4) (actual time=1053.867..2323.809 rows=100 loops=1) Buffers: shared hit=1799709 read=297868 -> Gather Merge (cost=1001.16..520607739.31 rows=3753366 width=4) (actual time=1053.865..2323.786 rows=100 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=1799709 read=297868 -> Nested Loop (cost=1.14..520173507.72 rows=1563902 width=4) (actual time=1019.601..2001.823 rows=46 loops=3) Buffers: shared hit=1799709 read=297868 -> Parallel Index Scan Backward using cache_index_id on cache_table t1 (cost=0.57..118451325.04 rows=62600747 width=8) (actual time=0.035..139.189 rows=124142 loops=3) Buffers: shared hit=124767 read=108346 -> Index Scan using houses_pkey on houses t2 (cost=0.56..6.42 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=372426) Index Cond: (id = t1.id_house) Filter: (id_addresses = ANY ('{1300337,...}'::integer[])) Rows Removed by Filter: 1 Buffers: shared hit=1674942 read=189522 Planning Time: 4.559 ms Execution Time: 2323.861 ms И в нём: -> Index Scan using houses_pkey on houses t2 (cost=0.56..6.42 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=372426) Т.е. для того, чтобы найти 100 подходящих записей, просканировать приходится 372426. По-моему, это не "довольно скоро", да и неизвестно, "как повезёт" для следующих 100 (эти LIMIT ... OFFSET тут для pagination). А вот вариант с выборкой по houses: > Index Scan using houses_index on houses t2 (cost=0.56..851821.48 rows=970332 width=4) (actual time=0.922..161.247 rows=71594 loops=5) Т.е. вполне может быть лучше "сначала всё выбрать, потом сортировать".

Если это будет 372426 index-only access, или, точнее index+hash join filter в памяти -- то это гораздо меньшэ, чем 70k index+heap access. А в том случае -- там таки был index access на cache, по нему index access на house и для него heap access на house. Хотя, конечно, учитывая что конкретно сейчас почти всё в памяти -- можэт и не будет это выгодным.

Кстати, три момента подумалось тут с утра. Первый -- что если это всё равно cache какой-то (ну, основная таблица так называется), и отношэния к houses у него подчинённые (id-то там primary key) -- то можно id_addresses в этот кэш запихивать. Не то, чтобы сильно поможэт в скорости (там и так вроде достаточно быстро) -- но как минимум снимет немного всяких плохих возможностей построения запроса. И ещё можно будет попробовать всякие изощрённые индэксы поприменять, чтобы всё-таки в ограниченное количество page access это укладывалось. Второй -- это что не всякий клиент руками набьёт тысячу индэксов.... А там точно нет каких-то справочников, из которых эти громадные списки берутся? А то если есть -- то можно как раз значение этого справочника добавить в таблицу, и искать по нему. Тогда по индэксу (справочник_id, index_id) оно искаться будет элементарно. И третий -- если справочников нет, запросы большые, клиенты тожэ автоматы, но паджынацыя, вот это всё -- то можно результаты запросов кэшыровать на сервере. На полчаса там, допустим, не знаю. Забабахать криптографических хэш от условия (списка индэксов, сортированного), по нему выбирать -- есть ужэ результат в кэшэ или как. Во временных таблицах (или во временной таблицэ). У которой ужэ будет индэкс прямо по результатам отсортированный.

Не на то это всё рассчитывалось, но можно попробовать так и так. А у меня с утра появился вопрос к @daniilak — а мы вообще настоящий запрос оптимизируем? Неужели там действительно выбирается только t1.index_id? И если да, то ORDER BY t1.index_id DESC тупо не подходит для pagination — это же не даёт детерминированного порядка выборки.

Благодарю. Клиенты еще пока не понятно, как они будут смотреть. Справочник вполне себе можно добавить, надо только подумать Возможно еще обращусь с новыми идеями)

Привет снова! А я подумал еще про одну деталь У меня в таблице cache_house есть поля: year, month и day. Все типа smallint. Кстати, надо просто создать взамен 1 колонку типа date. То я могу сделать ключ по дате +index_id + house_id и тогда в случае поиска сделать обязательным выбор промежутка даты, то тогда будет всё круто. Еще можно будет закешировать на каждый запрос общее количество элементов, чтобы показать, мол, в других датах еще есть записи