Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
Доброго времени суток.
Заранее извиняюсь за возможные некорректные формулировки. С PostgreSQL знаком недолго.
PostgreSQL использую в связке с Python (psycopg2). Сервер тестовый (не самый быстрый 2Гц 2Гб винты обычные)
Процесс таков:
1) Загружаю данные из csv формата с помощью copy from STDIN во временную таблицу (create temporary table my_table1 ...). Вставляется порядка 100-200 тыс. записей. Скорость COPY устраивает (около 5-10 секунд)
2) Эту времянку джойню с несколькими существующими (фильтрую как бы и некоторые значения подменяю) и результат пытаюсь вставить в результирующую таблицу с помощью INSERT INTO. Сам запрос выполняется 5-7 секунд. А вот со вставкой 2-3 минуты. (Записей 80-160 тыс.) Это неприемлимая скорость.

Помогите пож-та разобраться с такой скоростью. И какие есть варианты в моём случае. Заранее спасибо.
16 апр 09, 14:43    [7073748]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2891
Daster
Сам запрос выполняется 5-7 секунд. А вот со вставкой 2-3 минуты.
покажите EXPLAIN ANALYZE SELECT ... и EXPLAIN ANALYZE INSERT ...
16 апр 09, 14:48    [7073773]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
EXPLAIN ANALYZE SELECT
"Nested Loop  (cost=2160.86..2405.67 rows=1 width=38) (actual time=5953.205..5953.205 rows=0 loops=1)"
"  Join Filter: (y.id = spo.tmp_id)"
"  ->  Hash Join  (cost=2160.86..2404.65 rows=1 width=38) (actual time=2828.142..3820.913 rows=60399 loops=1)"
"        Hash Cond: (((hotel_sync.other_id)::text = (x.hotel_id)::text) AND (hotel_sync.oper_id = y.oper_id))"
"        ->  Seq Scan on sync hotel_sync  (cost=0.00..204.39 rows=5252 width=19) (actual time=0.075..56.469 rows=5242 loops=1)"
"              Filter: ((kind)::text = 'hotel'::text)"
"        ->  Hash  (cost=2160.79..2160.79 rows=5 width=54) (actual time=2788.698..2788.698 rows=76799 loops=1)"
"              ->  Hash Join  (cost=461.26..2160.79 rows=5 width=54) (actual time=42.797..2266.185 rows=76799 loops=1)"
"                    Hash Cond: ((y.oper_id = room_sync.oper_id) AND ((x.room_id)::text = (room_sync.other_id)::text))"
"                    ->  Hash Join  (cost=210.23..1905.78 rows=51 width=49) (actual time=10.563..1547.101 rows=76799 loops=1)"
"                          Hash Cond: ((y.oper_id = op.oper_id) AND ((x.place_id)::text = (op.oper_place_id)::text))"
"                          ->  Hash Join  (cost=207.91..1899.26 rows=240 width=45) (actual time=10.205..949.143 rows=76799 loops=1)"
"                                Hash Cond: ((x.meal_id)::text = (meal_sync.other_id)::text)"
"                                ->  Seq Scan on tmp_price x  (cost=0.00..1400.99 rows=76799 width=31) (actual time=0.049..270.086 rows=76799 loops=1)"
"                                ->  Hash  (cost=207.68..207.68 rows=18 width=27) (actual time=10.087..10.087 rows=33 loops=1)"
"                                      ->  Nested Loop  (cost=0.00..207.68 rows=18 width=27) (actual time=1.040..9.595 rows=33 loops=1)"
"                                            Join Filter: (y.oper_id = meal_sync.oper_id)"
"                                            ->  Seq Scan on tmp_spo y  (cost=0.00..1.01 rows=1 width=8) (actual time=0.032..0.035 rows=1 loops=1)"
"                                            ->  Seq Scan on sync meal_sync  (cost=0.00..204.39 rows=183 width=19) (actual time=0.039..9.026 rows=196 loops=1)"
"                                                  Filter: ((meal_sync.kind)::text = 'meal'::text)"
"                          ->  Hash  (cost=1.53..1.53 rows=53 width=13) (actual time=0.277..0.277 rows=53 loops=1)"
"                                ->  Seq Scan on dic_oper_place op  (cost=0.00..1.53 rows=53 width=13) (actual time=0.032..0.140 rows=53 loops=1)"
"                    ->  Hash  (cost=204.39..204.39 rows=3109 width=19) (actual time=32.162..32.162 rows=3095 loops=1)"
"                          ->  Seq Scan on sync room_sync  (cost=0.00..204.39 rows=3109 width=19) (actual time=0.050..19.319 rows=3095 loops=1)"
"                                Filter: ((kind)::text = 'room'::text)"
"  ->  Seq Scan on dic_spo spo  (cost=0.00..1.01 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=60399)"
"Total runtime: 5954.372 ms"

16 апр 09, 14:54    [7073808]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
EXPLAIN ANALYZE INSERT
"Nested Loop  (cost=2160.86..2405.68 rows=1 width=38) (actual time=2552.562..11737.482 rows=60399 loops=1)"
"  Join Filter: (y.id = spo.tmp_id)"
"  ->  Hash Join  (cost=2160.86..2404.65 rows=1 width=38) (actual time=2552.177..4080.641 rows=60399 loops=1)"
"        Hash Cond: (((hotel_sync.other_id)::text = (x.hotel_id)::text) AND (hotel_sync.oper_id = y.oper_id))"
"        ->  Seq Scan on sync hotel_sync  (cost=0.00..204.39 rows=5252 width=19) (actual time=0.061..23.493 rows=5242 loops=1)"
"              Filter: ((kind)::text = 'hotel'::text)"
"        ->  Hash  (cost=2160.79..2160.79 rows=5 width=54) (actual time=2531.546..2531.546 rows=76799 loops=1)"
"              ->  Hash Join  (cost=461.26..2160.79 rows=5 width=54) (actual time=48.153..2067.663 rows=76799 loops=1)"
"                    Hash Cond: ((y.oper_id = room_sync.oper_id) AND ((x.room_id)::text = (room_sync.other_id)::text))"
"                    ->  Hash Join  (cost=210.23..1905.78 rows=51 width=49) (actual time=16.247..1450.811 rows=76799 loops=1)"
"                          Hash Cond: ((y.oper_id = op.oper_id) AND ((x.place_id)::text = (op.oper_place_id)::text))"
"                          ->  Hash Join  (cost=207.91..1899.26 rows=240 width=45) (actual time=15.887..882.845 rows=76799 loops=1)"
"                                Hash Cond: ((x.meal_id)::text = (meal_sync.other_id)::text)"
"                                ->  Seq Scan on tmp_price x  (cost=0.00..1400.99 rows=76799 width=31) (actual time=0.018..256.735 rows=76799 loops=1)"
"                                ->  Hash  (cost=207.68..207.68 rows=18 width=27) (actual time=15.798..15.798 rows=33 loops=1)"
"                                      ->  Nested Loop  (cost=0.00..207.68 rows=18 width=27) (actual time=0.816..15.663 rows=33 loops=1)"
"                                            Join Filter: (y.oper_id = meal_sync.oper_id)"
"                                            ->  Seq Scan on tmp_spo y  (cost=0.00..1.01 rows=1 width=8) (actual time=0.007..0.011 rows=1 loops=1)"
"                                            ->  Seq Scan on sync meal_sync  (cost=0.00..204.39 rows=183 width=19) (actual time=0.037..15.105 rows=196 loops=1)"
"                                                  Filter: ((meal_sync.kind)::text = 'meal'::text)"
"                          ->  Hash  (cost=1.53..1.53 rows=53 width=13) (actual time=0.276..0.276 rows=53 loops=1)"
"                                ->  Seq Scan on dic_oper_place op  (cost=0.00..1.53 rows=53 width=13) (actual time=0.024..0.142 rows=53 loops=1)"
"                    ->  Hash  (cost=204.39..204.39 rows=3109 width=19) (actual time=31.858..31.858 rows=3095 loops=1)"
"                          ->  Seq Scan on sync room_sync  (cost=0.00..204.39 rows=3109 width=19) (actual time=0.039..19.523 rows=3095 loops=1)"
"                                Filter: ((kind)::text = 'room'::text)"
"  ->  Seq Scan on dic_spo spo  (cost=0.00..1.01 rows=1 width=8) (actual time=0.021..0.029 rows=1 loops=60399)"
"Total runtime: 24539.259 ms"
16 апр 09, 14:59    [7073833]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
Здесь я небольшую выборку представил 76000 записей. Никаких индексов на таблицу куда вставляются данные нет. Есть только primary key и unique ограничение.
16 апр 09, 15:01    [7073846]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
Сам запрос
insert into price_source (spo_id, hotel_id, room_id, meal_id, dt1, dt2, tarif, place_id, price)
select spo.id as spo_id, hotel_sync.my_id as hotel_id, room_sync.my_id as room_id, meal_sync.my_id as meal_id,
a.dt1, a.dt2, a.tarif, op.place_id, a.price
from 
(
  select y.id as spo_id, y.oper_id, x.* from tmp_price x inner join tmp_spo y on true
) a
inner join dic_spo spo on spo.tmp_id=a.spo_id
inner join sync hotel_sync on hotel_sync.kind='hotel' and hotel_sync.other_id=a.hotel_id and a.oper_id=hotel_sync.oper_id
inner join sync room_sync on room_sync.kind='room' and room_sync.other_id=a.room_id and a.oper_id=room_sync.oper_id
inner join sync meal_sync on meal_sync.kind='meal' and meal_sync.other_id=a.meal_id and a.oper_id=meal_sync.oper_id
inner join dic_oper_place op on op.oper_place_id=a.place_id and op.oper_id=a.oper_id;
16 апр 09, 15:03    [7073857]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
Удалил primary key и unique constraint вставка данных по времени стала примерно равной запросу.
Кстати, может кто подскажет, мне кажется что и запрос сам не оптимален?
16 апр 09, 15:39    [7074083]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2891
автор
Никаких индексов на таблицу куда вставляются данные нет. Есть только primary key и unique ограничение.
можете показать pg_dump -s? к сообщению в форуме можно приложить файл.

автор
EXPLAIN ANALYZE SELECT

"Nested Loop (cost=2160.86..2405.67 rows=1 width=38) (actual time=5953.205..5953.205 rows=0 loops=1)"
не понятно, почему actual rows=0.

автор
EXPLAIN ANALYZE INSERT

...

"Total runtime: 24539.259 ms"
вставка выполнялась 25 секунд?

автор
Кстати, может кто подскажет, мне кажется что и запрос сам не оптимален?
попробуйте индекс hotel_sync (other_id, oper_id)
16 апр 09, 15:50    [7074136]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
Да выполнялась 25 секунд. После удаления Primary Key и Unique Constraint вставка нормально стала работать. Я так пока оставлю поскольку эти ограничения не очень нужны были. Сейчас попробую индекс вставить.
16 апр 09, 15:55    [7074169]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
После вставки индекса все стало хуже
"Nested Loop  (cost=463.39..2197.05 rows=1 width=38) (actual time=382.502..11271.095 rows=60399 loops=1)"
"  Join Filter: (y.id = spo.tmp_id)"
"  ->  Nested Loop  (cost=463.39..2196.03 rows=1 width=38) (actual time=382.439..8021.775 rows=60399 loops=1)"
"        ->  Hash Join  (cost=463.39..2162.87 rows=4 width=54) (actual time=29.179..2614.409 rows=76799 loops=1)"
"              Hash Cond: ((y.oper_id = room_sync.oper_id) AND ((x.room_id)::text = (room_sync.other_id)::text))"
"              ->  Hash Join  (cost=210.43..1906.31 rows=53 width=49) (actual time=6.174..1773.027 rows=76799 loops=1)"
"                    Hash Cond: ((y.oper_id = op.oper_id) AND ((x.place_id)::text = (op.oper_place_id)::text))"
"                    ->  Hash Join  (cost=208.11..1899.63 rows=249 width=45) (actual time=5.805..1099.802 rows=76799 loops=1)"
"                          Hash Cond: ((x.meal_id)::text = (meal_sync.other_id)::text)"
"                          ->  Seq Scan on tmp_price x  (cost=0.00..1400.99 rows=76799 width=31) (actual time=0.017..234.474 rows=76799 loops=1)"
"                          ->  Hash  (cost=207.86..207.86 rows=20 width=28) (actual time=5.712..5.712 rows=33 loops=1)"
"                                ->  Nested Loop  (cost=0.00..207.86 rows=20 width=28) (actual time=0.682..5.582 rows=33 loops=1)"
"                                      Join Filter: (y.oper_id = meal_sync.oper_id)"
"                                      ->  Seq Scan on tmp_spo y  (cost=0.00..1.01 rows=1 width=8) (actual time=0.011..0.015 rows=1 loops=1)"
"                                      ->  Seq Scan on sync meal_sync  (cost=0.00..204.39 rows=197 width=20) (actual time=0.037..5.057 rows=196 loops=1)"
"                                            Filter: ((meal_sync.kind)::text = 'meal'::text)"
"                    ->  Hash  (cost=1.53..1.53 rows=53 width=13) (actual time=0.273..0.273 rows=53 loops=1)"
"                          ->  Seq Scan on dic_oper_place op  (cost=0.00..1.53 rows=53 width=13) (actual time=0.024..0.130 rows=53 loops=1)"
"              ->  Hash  (cost=204.39..204.39 rows=3238 width=20) (actual time=22.786..22.786 rows=3095 loops=1)"
"                    ->  Seq Scan on sync room_sync  (cost=0.00..204.39 rows=3238 width=20) (actual time=0.047..11.450 rows=3095 loops=1)"
"                          Filter: ((kind)::text = 'room'::text)"
"        ->  Index Scan using ix_sync on sync hotel_sync  (cost=0.00..8.27 rows=1 width=20) (actual time=0.049..0.055 rows=1 loops=76799)"
"              Index Cond: (((hotel_sync.other_id)::text = (x.hotel_id)::text) AND (hotel_sync.oper_id = y.oper_id))"
"              Filter: ((hotel_sync.kind)::text = 'hotel'::text)"
"  ->  Seq Scan on dic_spo spo  (cost=0.00..1.01 rows=1 width=8) (actual time=0.009..0.012 rows=1 loops=60399)"
"Total runtime: 11417.939 ms"
16 апр 09, 15:59    [7074200]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
Вот план без индексов
"Nested Loop  (cost=2159.05..2403.86 rows=1 width=38) (actual time=1817.481..4020.702 rows=60399 loops=1)"
"  Join Filter: (y.id = spo.tmp_id)"
"  ->  Hash Join  (cost=2159.05..2402.84 rows=1 width=38) (actual time=1817.410..2307.134 rows=60399 loops=1)"
"        Hash Cond: (((hotel_sync.other_id)::text = (x.hotel_id)::text) AND (hotel_sync.oper_id = y.oper_id))"
"        ->  Seq Scan on sync hotel_sync  (cost=0.00..204.39 rows=5252 width=19) (actual time=0.050..14.363 rows=5242 loops=1)"
"              Filter: ((kind)::text = 'hotel'::text)"
"        ->  Hash  (cost=2158.99..2158.99 rows=4 width=54) (actual time=1802.852..1802.852 rows=76799 loops=1)"
"              ->  Hash Join  (cost=460.99..2158.99 rows=4 width=54) (actual time=30.435..1495.598 rows=76799 loops=1)"
"                    Hash Cond: ((y.oper_id = room_sync.oper_id) AND ((x.room_id)::text = (room_sync.other_id)::text))"
"                    ->  Hash Join  (cost=210.23..1905.84 rows=50 width=49) (actual time=6.044..1082.448 rows=76799 loops=1)"
"                          Hash Cond: ((y.oper_id = op.oper_id) AND ((x.place_id)::text = (op.oper_place_id)::text))"
"                          ->  Hash Join  (cost=207.91..1899.28 rows=242 width=45) (actual time=5.707..613.725 rows=76799 loops=1)"
"                                Hash Cond: ((x.meal_id)::text = (meal_sync.other_id)::text)"
"                                ->  Seq Scan on tmp_price x  (cost=0.00..1400.99 rows=76799 width=31) (actual time=0.016..155.438 rows=76799 loops=1)"
"                                ->  Hash  (cost=207.68..207.68 rows=18 width=27) (actual time=5.608..5.608 rows=33 loops=1)"
"                                      ->  Nested Loop  (cost=0.00..207.68 rows=18 width=27) (actual time=0.678..5.486 rows=33 loops=1)"
"                                            Join Filter: (y.oper_id = meal_sync.oper_id)"
"                                            ->  Seq Scan on tmp_spo y  (cost=0.00..1.01 rows=1 width=8) (actual time=0.011..0.015 rows=1 loops=1)"
"                                            ->  Seq Scan on sync meal_sync  (cost=0.00..204.39 rows=183 width=19) (actual time=0.037..4.959 rows=196 loops=1)"
"                                                  Filter: ((meal_sync.kind)::text = 'meal'::text)"
"                          ->  Hash  (cost=1.53..1.53 rows=53 width=13) (actual time=0.255..0.255 rows=53 loops=1)"
"                                ->  Seq Scan on dic_oper_place op  (cost=0.00..1.53 rows=53 width=13) (actual time=0.019..0.123 rows=53 loops=1)"
"                    ->  Hash  (cost=204.39..204.39 rows=3091 width=19) (actual time=24.337..24.337 rows=3095 loops=1)"
"                          ->  Seq Scan on sync room_sync  (cost=0.00..204.39 rows=3091 width=19) (actual time=0.041..11.582 rows=3095 loops=1)"
"                                Filter: ((kind)::text = 'room'::text)"
"  ->  Seq Scan on dic_spo spo  (cost=0.00..1.01 rows=1 width=8) (actual time=0.005..0.008 rows=1 loops=60399)"
"Total runtime: 4135.964 ms"

Подскажите куда копать чтобы оптимизировать?
16 апр 09, 16:00    [7074213]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
Там одна таблица 3 раза джойнится с разными параметрами.
16 апр 09, 16:02    [7074229]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2891
Daster
После удаления Primary Key и Unique Constraint вставка нормально стала работать. Я так пока оставлю поскольку эти ограничения не очень нужны были.
попробуйте создать pk и uk, они могли "замусориться", и после пересоздания может быстро работать.

автор
После вставки индекса все стало хуже
можно попробовать set enable_nestloop to off;
16 апр 09, 16:02    [7074237]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
Создал индекс. Выключил вложеные циклы и создал ограничения
"Hash Join  (cost=2160.13..2403.82 rows=1 width=38) (actual time=1763.467..4436.441 rows=60399 loops=1)"
"  Hash Cond: (y.id = spo.tmp_id)"
"  ->  Hash Join  (cost=2159.11..2402.78 rows=1 width=38) (actual time=1762.975..2574.028 rows=60399 loops=1)"
"        Hash Cond: (((hotel_sync.other_id)::text = (x.hotel_id)::text) AND (hotel_sync.oper_id = y.oper_id))"
"        ->  Seq Scan on sync hotel_sync  (cost=0.00..204.39 rows=5237 width=19) (actual time=0.027..14.161 rows=5242 loops=1)"
"              Filter: ((kind)::text = 'hotel'::text)"
"        ->  Hash  (cost=2159.05..2159.05 rows=4 width=54) (actual time=1749.870..1749.870 rows=76799 loops=1)"
"              ->  Hash Join  (cost=460.07..2159.05 rows=4 width=54) (actual time=24.422..1439.074 rows=76799 loops=1)"
"                    Hash Cond: ((y.oper_id = room_sync.oper_id) AND ((x.room_id)::text = (room_sync.other_id)::text))"
"                    ->  Hash Join  (cost=208.83..1904.41 rows=52 width=49) (actual time=5.516..988.732 rows=76799 loops=1)"
"                          Hash Cond: ((y.oper_id = op.oper_id) AND ((x.place_id)::text = (op.oper_place_id)::text))"
"                          ->  Hash Join  (cost=206.51..1897.84 rows=242 width=45) (actual time=5.157..561.901 rows=76799 loops=1)"
"                                Hash Cond: ((x.meal_id)::text = (meal_sync.other_id)::text)"
"                                ->  Seq Scan on tmp_price x  (cost=0.00..1400.99 rows=76799 width=31) (actual time=0.016..162.695 rows=76799 loops=1)"
"                                ->  Hash  (cost=206.28..206.28 rows=18 width=27) (actual time=5.074..5.074 rows=33 loops=1)"
"                                      ->  Hash Join  (cost=1.02..206.28 rows=18 width=27) (actual time=0.745..4.959 rows=33 loops=1)"
"                                            Hash Cond: (meal_sync.oper_id = y.oper_id)"
"                                            ->  Seq Scan on sync meal_sync  (cost=0.00..204.39 rows=185 width=19) (actual time=0.037..4.379 rows=196 loops=1)"
"                                                  Filter: ((kind)::text = 'meal'::text)"
"                                            ->  Hash  (cost=1.01..1.01 rows=1 width=8) (actual time=0.032..0.032 rows=1 loops=1)"
"                                                  ->  Seq Scan on tmp_spo y  (cost=0.00..1.01 rows=1 width=8) (actual time=0.014..0.018 rows=1 loops=1)"
"                          ->  Hash  (cost=1.53..1.53 rows=53 width=13) (actual time=0.267..0.267 rows=53 loops=1)"
"                                ->  Seq Scan on dic_oper_place op  (cost=0.00..1.53 rows=53 width=13) (actual time=0.023..0.128 rows=53 loops=1)"
"                    ->  Hash  (cost=204.39..204.39 rows=3123 width=19) (actual time=18.846..18.846 rows=3095 loops=1)"
"                          ->  Seq Scan on sync room_sync  (cost=0.00..204.39 rows=3123 width=19) (actual time=0.041..11.025 rows=3095 loops=1)"
"                                Filter: ((kind)::text = 'room'::text)"
"  ->  Hash  (cost=1.01..1.01 rows=1 width=8) (actual time=0.037..0.037 rows=1 loops=1)"
"        ->  Seq Scan on dic_spo spo  (cost=0.00..1.01 rows=1 width=8) (actual time=0.020..0.023 rows=1 loops=1)"
"Total runtime: 11670.210 ms"
16 апр 09, 16:11    [7074293]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
Без PrimaryKey и unique вставка теперь 3.5 секунды.
Кстати в этой таблице (куда вставляем) впоследствии будет данных порядка 400-500 млн причем добавляться будет по 1 млн в среднем в неделю. Если я оставлю эту таблицу без Primary Key. Как то скажется скорость выборок на этом?
16 апр 09, 16:18    [7074338]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Ёш
Member

Откуда:
Сообщений: 2892
Daster

Процесс таков:
1) Загружаю данные из csv формата с помощью copy from STDIN во временную таблицу (create temporary table my_table1 ...). Вставляется порядка 100-200 тыс. записей. Скорость COPY устраивает (около 5-10 секунд)
2) Эту времянку джойню с несколькими существующими (фильтрую как бы и некоторые значения подменяю) и результат пытаюсь вставить в результирующую таблицу с помощью INSERT INTO. Сам запрос выполняется 5-7 секунд. А вот со вставкой 2-3 минуты. (Записей 80-160 тыс.) Это неприемлимая скорость.
а если после пункта 1 и перед пунктом 2 выполнить:
--- собираем статистику распределения данных в Вашей временной таблице
--- для планировщика запросов
analyze my_table1;
мне кажется что 200 тысяч строк и их использование во нескольких join'ах уже требуют нормальной статистики для оптимального планирования запроса.
16 апр 09, 16:20    [7074352]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
assa
Member [заблокирован]

Откуда: мы сами не местные
Сообщений: 3444
LeXa NalBat
Daster
После удаления Primary Key и Unique Constraint вставка нормально стала работать. Я так пока оставлю поскольку эти ограничения не очень нужны были.
попробуйте создать pk и uk, они могли "замусориться", и после пересоздания может быстро работать.
гм. не совсем понятно, с какой таблички автор снес пк и ук. если с таблички назначения - то да, работать будет быстрее. а "не очень нужно" - это из разряда когда автор сам себе буратинка. Или нужно - или нет. Если таки нужно - то можно сделать Vacuum ANALYZE данных табличек _перед вставкой_ (ну или REINEX).

Обычно я так и делаю (у меня перед заливкой данных из буферных табличек делетится изрядная пачка данных (не транкейтиться, поскольку много разных путей заливки, могущих работать одновременно), а индексы на промежуточные таблички таки мне нужны - много отбрасывается по ним. Вот аккурат после делета и перед вставкой я и вакуум-анализирую конкретные таблички. Если это дело не запускать - быстро пролетает).

Когда-то давно просматривал вариант сноса индексов-ограничений до вставки и поднятия после - не понравилось. и дороже, и ф-ии в том же сеансе ошибались (на oid-ах индексов).
16 апр 09, 16:21    [7074356]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
analyze my_table1
Для временной таблицы делается

По поводу ключей. Я пока пытаюсь еще только спрогнозировать как будет работать база, а посколько опыта конкретно с Postgres не имею, то и мечусь. Вопрос про нужность Primary key все еще актуален.
16 апр 09, 16:32    [7074433]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2891
Daster
Если я оставлю эту таблицу без Primary Key. Как то скажется скорость выборок на этом?
зависит от выборок. для ускорения определённых выборок можно будет создать индекс (по тем же полям, что удалённый primary key).
16 апр 09, 16:40    [7074475]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
LeXa NalBat
Member

Откуда: Москва
Сообщений: 2891
Daster
Вот план без индексов

"Total runtime: 4135.964 ms"

Подскажите куда копать чтобы оптимизировать?
косяков в плане не видно. может взглянуть на hdd, память, настройки постгреса?
16 апр 09, 16:42    [7074484]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Warstone
Member

Откуда:
Сообщений: 4896
Блог
Индекс по sync (kind, other_id, oper_id) должен помочь. Так как таким образом 3 джойна будут работать быстрее.
16 апр 09, 17:08    [7074638]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Daster
Member

Откуда:
Сообщений: 56
Всем спасибо за участие (особенно LeXa NalBat - мне бы так научиться планы читать). Пока решил остановиться на варианте без Primary key и unique constraint для таблицы куда вставляются данные. Для таблицы sync (джойнится 3 раза к времянке) пока оставлю индекс по (other_id и oper_id).
Протестировал на времянке в 300 тыс. записей - время вставки 12 сек - меня такое время устраивает. А все остальные проблемы с выборками буду решать по мере поступления.
16 апр 09, 18:52    [7075256]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: INSERT INTO ... SELECT медленная вставка  [new]
IvanC
Member

Откуда:
Сообщений: 103
Пожалуй, подниму тему. Тот же самый INSERT INTO SELECT работает ну просто неприлично долго (10 секунд) при условии, что SELECT выполняется 300 мс, при этом CREATE AS SELECT выполняется столько же (300мс). Это сам запрос:
EXPLAIN ANALYZE INSERT INTO lo (d, l, c, a, t, n, h, r) SELECT DISTINCT l.d, l.l, l.c, COALESCE(b.a, '-') as ad, COALESCE(b.t::text, '-') as t, COALESCE(b.n::text, '-') as n, COALESCE(b.a::text, '-') as t, r.f FROM lt.s as l JOIN bg as b ON l.l = b.l AND l.c = b.c JOIN rs as r ON l.rid = r.id WHERE l.tid = 357894 ORDER BY l.bd LIMIT 10;

Тут происходит затык:
"                                ->  Merge Join  (cost=991480.19..1006776.34 rows=1428 width=221) (actual time=10052.380..10077.233 rows=522 loops=1)"
"                                      Merge Cond: (((l.l)::text = (b.l)::text) AND ((l.c)::text = (b.c)::text))"
"                                      ->  Sort  (cost=496883.42..497342.81 rows=183756 width=25) (actual time=294.107..294.161 rows=526 loops=1)"
"                                            Sort Key: l.l, l.c"
"                                            Sort Method: quicksort  Memory: 66kB"

Подскажите, пожалуйста, в чем может быть проблема?
29 янв 20, 12:32    [22068523]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Troglodit
Member

Откуда:
Сообщений: 498
Вы хотите сказать, что для вставки 10 записей в таблицу 10с вина именно insert?
Если вы там в триггерах ничего не майните и диски не битые, нет хитрых проверок, то вряд ли. Скорее всего проблема в select.
29 янв 20, 14:17    [22068640]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
IvanC
Member

Откуда:
Сообщений: 103
Но отдельно же он отрабатывает за 300 мс, таблица, в которую я вставляю UNLOGGED, с триггерами не игрался и диски, хоть и старые в RAIDе, но подозрений не вызывают(копирую по сети на них со скоростью 30 Мбайт/сек)... Вот и я уже голову сломал, почему так...
29 янв 20, 14:51    [22068682]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
IvanC
Member

Откуда:
Сообщений: 103
Забыл добавить, что таблица без ключей и индексов, да и сам INSERT, судя по анализу, проходит быстро:
Insert on lo  (cost=1012040.65..1012041.02 rows=10 width=378) (actual time=9908.589..9908.589 rows=0 loops=1)
29 янв 20, 18:11    [22068938]     Ответить | Цитировать Сообщить модератору
 Re: INSERT INTO ... SELECT медленная вставка  [new]
Troglodit
Member

Откуда:
Сообщений: 498
IvanC,
Ну так покажите 2 плана, только select и с insert.
Чего стесняться то? Телепаты в отпуске.
29 янв 20, 20:05    [22069024]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / PostgreSQL Ответить