Как правильно настроить базу\табилцу для выборки большого кол-ва данных?

Question

[Sergei Iamskoi]

Есть СУБД PostgreSQL 9.5, настроена на текущий проект по статьям из интернетов. Понадобилось добавить в базу таблицу (список недействительных паспартов с сайта ФМС) с одним полем varchar(10) и кол-вом записей 100 млн. Запрос вида

SELECT * FROM expiredPassports WHER serial = '1234123456';

занимает в среднем 15 секунд. BIGINT тоже ставили, результат на 1-2 секунды лучше.
Что и как правильно настроить, что бы селект был правильным и укладывался сотые или хотя бы десятые секунды?
Таблица в основном только на чтение, обновление планируется раз в неделю.

Comments

[vovik0134]

План покажите.

Answer 1

[sasha]

зачем тебе BIGINT? обычного инта должно хватать, делаешь его primary key, если скорость все еще будет нехватать то делай шарды миллионов по 10.

Comments

[sim3x]

в праймари лучше не хранить данные

[sasha]

sim3x: почему?

[Sergei Iamskoi]

В INTGERER не велезет - максимум 4 294 967 294 против 9 999 999 999 у номера паспорта. Если только на два столбца разбить номер и серия) - кстати, это увеличит скорость?

[sim3x]

sasha: праймари - служебная информация

[sasha]

Sergey Yamskoy: да, не сообразил. Тогда скорей всего тебе поможет шардование. Остальное что я могу тебе сказать это догадки и лирика.

[sasha]

sim3x: да, если там стоит автоинкремент. А если у меня есть информация которая по своей сущности является уникальной и наличие не уникальной такой информации является фатальной ошибкой ?

[sim3x]

sasha: сущность над которой ты не имеешь контроля не может иметь свойство уникальности

[sasha]

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

[sim3x]

sasha: подразумевается, что работаем в 3нф/нф бк

[lega]

sim3x: primary key как раз уникален по дефолту, его как раз тут и надо юзать, что-б лишние индекссы не плодить, а топик стартер похоже вообще индексы не юзает.
+ не понятно зачем ему посгрес для этой задачи.
+ индекс лучше хешмап юзать вместо дерева для этой задачи

[sasha]

lega: номер паспорта по идее уникален, а на деле - хз и лучше перестраховаться. вот к чему предыдущий оратор вел.

[lega]

sasha: Если не уникален, то можно их стопочкой складывать в одной строке

[lega]

sasha: это и то если нужна какая-то доп. инфа, а судя по задаче вообще нужно только знать входит в список или нет, тут и таблицы не нужно, индекса достаточно.

[Sergei Iamskoi]

lega: индексы само собой используются

[lega]

Sergey Yamskoy: проверьте что весь индекс в памяти, судя по времени, он с диска индекс читает.
Индекс может занять 2-8Гб, если у вас 2 индекса в таблице - умножайте на 2.

Так же ещё можете попробовать hash индекс с доступом О(1), в посгресе он есть.

Если повозится, можно сделать хеш таблицу на основе 5байт ключей (на паспорт хватит), 600 - 800Мб.