Самый эффективный способ создания индекса в Postgres
Насколько эффективнее создавать индекс после завершения загрузки данных или раньше, или это не имеет значения?
Например, скажем, у меня есть 500 файлов для загрузки в DB Postgres 8.4. Вот два сценария создания сценариев, которые я мог бы использовать:
- Создавать индекс при создании таблицы, а затем загружать каждый файл в таблицу; или
- Создать индекс после того, как все файлы были загружены в таблицу.
Сама таблица данных составляет около 45 гигабайт. Индекс составляет около 12 гигабайт. Я использую стандартный индекс. Он создается следующим образом:
CREATE INDEX idx_name ON table_name (column_name);
Моя загрузка данных использует COPY FROM.
Как только все файлы будут загружены, в таблице не появятся обновления, удаления или дополнительные нагрузки (это день, когда данные не будут меняться). Поэтому я хотел спросить, какой сценарий будет наиболее эффективным? Первоначальное тестирование, по-видимому, указывает на то, что загрузка всех файлов, а затем создание индекса (сценарий 2) выполняется быстрее, но я не провел научного сравнения двух подходов.
Ответы
Ответ 1
Ваше наблюдение верное - гораздо эффективнее сначала загружать данные, а затем создавать индекс. Причина этого в том, что обновления индекса во время вставки дороги. Если вы создаете индекс после того, как все данные там, это намного быстрее.
Это идет еще дальше - если вам нужно импортировать большой объем данных в существующую индексированную таблицу, часто более эффективно сначала отбрасывать существующий индекс, импортировать данные, а затем снова создавать индекс.
Единственным недостатком создания индекса после импорта является то, что таблица должна быть заблокирована, и это может занять много времени (она не будет заблокирована в противоположном сценарии). Но в PostgreSQL 8.2 и более поздних версиях вы можете использовать CREATE INDEX CONCURRENTLY, который не блокирует таблицу при индексировании (с некоторыми оговорками).
