Как можно максимально ускорить выборку с низкой селективностью из таблицы с сотнями миллионов записей?

Question

[Ivanoff-i]

Есть таблица на несколько сотен миллионов записей. Около 200 Гб. В ней много столбцов, включая текстовые. Есть столбцы с низкой селективностью. Например, столбец город. Задача выбрать все, где город равен определенному значению. Например по СПБ порядка 10 млн. записей. Все их нужно вывести в файл. Т.е. запрос такого вида COPY (SELECT несколько полей ...) TO 'file.txt'. Сейчас они сохраняются полчаса. Никакие индексы не помогают. Причем если сделать не SELECT несколько полей, а SELECT id ... WHERE city = ..., то это происходит за несколько секунд. Если вынести записи по городу СПБ в отдельное материализованное представление, SELECT несколько полей ... происходит уже не полчаса, а полминуты.

1. Неужели нужно создавать под каждый город отдельные таблицы?
   
2. Если создавать отдельные таблицы под каждый город, то как быть если нужно фильтровать по другим столбцам, не по городу?
   
3. Читал немного про PgPool 2 и его возможность параллельных запросов. Если сделать партицирование по id и использовать параллельные запросы сразу ко всем партициям, вариант ли это? И сможет ли pgpool сделать это в рамках одной машины?
   
4. Как еще можно оптимизировать?
   
5. Справится ли вообще одна машина с этой задачей? Где-то читал, люди пишут, что у них пару миллиардов записей в постгресе летают на одной машине даже при достаточно сложных запросах. Как так?
   

Answer 1

[xmoonlight]

Приводите базу к ДНФ3.
Для текущего случая: все города должны быть в отдельной таблице - список городов с ID-шниками.

Comments

[Ivanoff-i]

Список городов с ID-шниками. Т.е. связь один к одному к основной таблице? И потом JOIN?

[xmoonlight]

Ivanoff-i: т.е. ID городов, вместо названий городов в основной таблице, а список всех названий - в другой таблице (перечень с ID-шниками).
И связь "один-ко-многим" (относительно таблицы городов и основной таблицы, соответственно).

[Ivanoff-i]

Дело в том, что города в основной таблице - это и есть их id с типом integer. Поможет ли в этом случае такой вариант?

[xmoonlight]

Ivanoff-i: схему данных - покажите, а то мы сферического коня в вакууме сейчас будем обсуждать.

[Ivanoff-i]

Вот такая схема

id bigint PRIMARY,
type character varying(20),
name character varying(400),
alias character varying(100),
count integer,
country integer,
city integer,
site character varying(255),
status character varying(30),
is_active smallint,
date character varying(20)

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

[xmoonlight]

Ivanoff-i:
1. все поля с ID - должны быть ключевыми.
2. создайте индексы для всех полей, по которым планируете делать поиск
затем - пробуйте выполнить поиск, не забывайте дописывать LIMIT в конце, если ищите одну (или известное кол-во) записей.

Answer 2

[Melkij]

Какие индексы есть? Структура таблицы?
explain (analyze, buffers)?

1. не нужно
2. см. 1
3. если вы упираетесь в CPU, а не в диск. Если в диск - сделает только хуже.
4. сначала выяснить, как ведёт себя имеющаяся табличка. Потом думать. Например, brin по id города. На низкоселективных полях получится внятный компактный индекс.
5. 200гб вполне себе нормальная база. Даже не астрономически дорого целиком в shared_buffers разместить.

Comments

[Ivanoff-i]

id bigint PRIMARY,
type character varying(20),
name character varying(400),
alias character varying(100),
count integer,
country integer,
city integer,
site character varying(255),
status character varying(30),
is_active smallint,
date character varying(20)

На поле name индекс gin(to_tsvector()). На остальные поля btree индекс. И один составной индекс еще решил попробовать тоже btree на поля id и city. Он в запросе и использовался.

EXPLAIN (ANALYZE,BUFFERS) SELECT id, name, alias, count FROM public.item WHERE city = 8

Bitmap Heap Scan on "item" (cost=103708.16..7024255.99 rows=5540592 width=57) (actual time=4566.896..1333276.255 rows=5640073 loops=1)
Recheck Cond: (city = 8)
Rows Removed by Index Recheck: 99736844
Heap Blocks: exact=13625 lossy=2174087
Buffers: shared read=2203126
-> Bitmap Index Scan on ix_item_city (cost=0.00..102323.01 rows=5540592 width=0) (actual time=4548.816..4548.816 rows=5640073 loops=1)
Index Cond: (city = 8)
Buffers: shared read=15414
Planning time: 113.841 ms
Execution time: 1334887.657 ms

[Ivanoff-i]

По поводу cpu и диска iostat показывал в пиках до 20 мб в сек. при том что все это на ssd. Cpu даже половина ядра не загружена из 6 ядер.

[Melkij]

100% промах по буферам, конечно холодную таблицу с диска читать медленно. Даже индекс с диска поднимать пришлось. И work_mem не хватило для bitmap, пришлось скатиться до битмапа по страницам и много recheck'ать по целым страничкам.
Поднимите work_mem для этого запроса. Не скажу на сколько, до пропадания Heap Blocks lossy, т.е. довольно ощутимо от текущего уровня.
Поднимать ли shared_buffers - надо понимать workload базы. Если это холодная табличка и запрос надо редко, можно не трогать. Если она должна быть более горячая - то увеличивать shared_buffers, возможно доставлять память.

> один составной индекс еще решил попробовать тоже btree на поля id и city
id & city? А чем он должен был хотя бы в теории помочь?

> На остальные поля btree индекс
На все? А зачем?

[Ivanoff-i]

Составной индекс в теории вряд ли мог чем-то помочь, но решил попробовать на практике. Вообще с индексами провожу эксперименты относительно недавно, поэтому опыта мало, прошу строго не судить. Индексы на остальные поля, потому что по остальным полям тоже нужна фильтрация в самых разных комбинациях.
По поводу того, часто ли нужен запрос. Дело в том, что условия в запросах могут быть по каким угодно столбцам и когда угодно. И хотелось бы чтобы они все быстро отрабатывали. На shared_buffers выделено 6 гигабайт памяти из доступных 18. work_mem в общем конфиге 5мб установлен. Я так понимаю, чтобы любые запросы отрабатывали быстро, таблица должна быть разогрета вся полностью, а чтобы ее разогреть, надо ее всю в память загнать. Т.е. взять серверов с суммарным количеством памяти под 200гб и делать шардинг, я правильно понимаю? Если так, то это не слишком дешево получается. Или все-таки есть другой способ?

[Melkij]

Почему не сильно дешево?
Взять одну железку на 256гб RAM и проблем с чтения с дисков почти нет.
Например, на ovh от $300 в месяц даже с парой SSD: https://www.ovh.com/us/dedicated-servers/

Скорей всего (я не знаю вашего workload!) вам вполне хватит и 128гб, а то и вообще 64гб под горячие данные. Столько памяти не проблема даже на десктоп поставить.

Можно попробовать один multicolumn gin повесить. Ему, в отличии от btree, порядок полей в индексе пофиг. Постоянно читать всё - всё равно быстро не будет. Чтобы работать адекватно в памяти должны быть горячие данные.

[Ivanoff-i]

workload 100% read only. Когда база обновляется, она закрыта для чтения. По поводу multicolumn gin пока не совсем понимаю как. Насколько я понимаю, gin ведь нельзя на обычные integer поля создать? И как в нем обстоит с операторами больше-меньше? Можно пример на основе тех полей, что я выше описал?

[Melkij]

Да, из коробки gin не умеет int, есть contrib btree_gin (есть и для gist аналог). Но похоже для пары интов уже имеет значение порядок полей. range по интам умеет.
Впрочем, bitmap index scan может и сам обойти несколько индексов. Не помню, как принято решать вопрос разнообразных комбинаций фильтров.