Медленная скорость вставки в табличной области памяти Postgresql

Быстрая загрузка данных

1. Переведите данные в CSV.
2. Создайте временную таблицу (как вы заметили, без индексов).
3. Выполните команду COPY: \ COPY schema.temp_table FROM /tmp/data.csv С CSV
4. Вставьте данные в невременную таблицу.
5. Создать индексы.
6. Установите соответствующую статистику.

Дополнительные рекомендации

Для больших объемов данных:

1. Разделите данные на дочерние таблицы.
2. Вставьте его в порядке столбца, в котором будет использоваться большинство операторов SELECT . Другими словами, попытайтесь согласовать физическую модель с логической.
3. Измените параметры конфигурации.
4. Создайте индекс CLUSTER (самый важный столбец слева). Например:

    CREATE UNIQUE INDEX measurement_001_stc_index
      ON climate.measurement_001
      USING btree
      (station_id, taken, category_id);
    ALTER TABLE climate.measurement_001 CLUSTER ON measurement_001_stc_index;

Настройки конфигурации

На машине с 4 ГБ ОЗУ я сделал следующее ...

Конфигурация ядра

Сообщите ядру, что программы могут использовать большие объемы общей памяти:

sysctl -w kernel.shmmax=536870912
sysctl -p /etc/sysctl.conf

Конфигурация PostgreSQL

1. Отредактируйте /etc/postgresql/8.4/main/postgresql.conf и установите:

   shared_buffers = 1 ГБ
   temp_buffers = 32 МБ
   work_mem = 32 МБ
   maintenance_work_mem = 64 МБ
   seq_page_cost = 1.0
   random_page_cost = 2.0
   cpu_index_tuple_cost = 0,001
   Effective_cache_size = 512 МБ
   checkpoint_segments = 10 

2. Настройте значения по мере необходимости и в соответствии с вашей средой. Возможно, позже вам придется изменить их для подходящей оптимизации чтения / записи.
3. Перезапустите PostgreSQL.

Дочерние таблицы

Например, у вас есть данные о погоде, разделенные на разные категории. Вместо того, чтобы иметь одну чудовищную таблицу, разделите ее на несколько таблиц (по одной на каждую категорию).

Основная таблица

CREATE TABLE climate.measurement
(
  id bigserial NOT NULL,
  taken date NOT NULL,
  station_id integer NOT NULL,
  amount numeric(8,2) NOT NULL,
  flag character varying(1) NOT NULL,
  category_id smallint NOT NULL,
  CONSTRAINT measurement_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

Дочерняя таблица

CREATE TABLE climate.measurement_001
(
-- Inherited from table climate.measurement_001:  id bigint NOT NULL DEFAULT nextval('climate.measurement_id_seq'::regclass),
-- Inherited from table climate.measurement_001:  taken date NOT NULL,
-- Inherited from table climate.measurement_001:  station_id integer NOT NULL,
-- Inherited from table climate.measurement_001:  amount numeric(8,2) NOT NULL,
-- Inherited from table climate.measurement_001:  flag character varying(1) NOT NULL,
-- Inherited from table climate.measurement_001:  category_id smallint NOT NULL,
  CONSTRAINT measurement_001_pkey PRIMARY KEY (id),
  CONSTRAINT measurement_001_category_id_ck CHECK (category_id = 1)
)
INHERITS (climate.measurement)
WITH (
  OIDS=FALSE
);

Статистика таблицы

Увеличьте статистику таблицы для важных столбцов:

ALTER TABLE climate.measurement_001 ALTER COLUMN taken SET STATISTICS 1000;
ALTER TABLE climate.measurement_001 ALTER COLUMN station_id SET STATISTICS 1000;

Не забудьте ВАКУУМ и АНАЛИЗ ] после.