INSERT INTO ... SELECT медленная вставка / PostgreSQL

2.0.53

Планшетная версия Контакт Правила FAQ Помощь

Гость

Нов. | Гор. | Избр.

Действия ...

Доб. в избранное
Игнор. тему
Прикреп. тему
Пометить прочит. / непрочит.
Фильтр:
Сообщения автора темы
Сообщение содержит вложения
Сообщение содержит картинки
Сообщение содержит видеоклипы
Сообщение содержит аудиоклипы
Сообщение содержит картинки или видео 18+

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / INSERT INTO ... SELECT медленная вставка / 25 сообщений из 27, страница 1 из 2

все

16.04.2009, 14:43

#35936300

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Доброго времени суток.
Заранее извиняюсь за возможные некорректные формулировки. С 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 тыс.) Это неприемлимая скорость.

Помогите пож-та разобраться с такой скоростью. И какие есть варианты в моём случае. Заранее спасибо.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 14:48

#35936316

LeXa NalBat

Участник

Откуда: Москва
Сообщения: 2 843
Рейтинг: 0 / 0

INSERT INTO ... SELECT медленная вставка

DasterСам запрос выполняется 5-7 секунд. А вот со вставкой 2-3 минуты.покажите EXPLAIN ANALYZE SELECT ... и EXPLAIN ANALYZE INSERT ...

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 14:54

#35936337

Daster

Гость

INSERT INTO ... SELECT медленная вставка

EXPLAIN ANALYZE SELECT

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

"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"

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 14:59

#35936350

Daster

Гость

INSERT INTO ... SELECT медленная вставка

EXPLAIN ANALYZE INSERT

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

"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"

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 15:01

#35936357

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Здесь я небольшую выборку представил 76000 записей. Никаких индексов на таблицу куда вставляются данные нет. Есть только primary key и unique ограничение.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 15:03

#35936364

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Сам запрос

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

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;

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 15:39

#35936476

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Удалил primary key и unique constraint вставка данных по времени стала примерно равной запросу.
Кстати, может кто подскажет, мне кажется что и запрос сам не оптимален?

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 15:50

#35936508

LeXa NalBat

Участник

Откуда: Москва
Сообщения: 2 843
Рейтинг: 0 / 0

INSERT INTO ... SELECT медленная вставка

авторНикаких индексов на таблицу куда вставляются данные нет. Есть только 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)

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 15:55

#35936524

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Да выполнялась 25 секунд. После удаления Primary Key и Unique Constraint вставка нормально стала работать. Я так пока оставлю поскольку эти ограничения не очень нужны были. Сейчас попробую индекс вставить.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 15:59

#35936541

Daster

Гость

INSERT INTO ... SELECT медленная вставка

После вставки индекса все стало хуже

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

"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"

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 16:00

#35936549

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Вот план без индексов

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

"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"

Подскажите куда копать чтобы оптимизировать?

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 16:02

#35936557

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Там одна таблица 3 раза джойнится с разными параметрами.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 16:02

#35936561

LeXa NalBat

Участник

Откуда: Москва
Сообщения: 2 843
Рейтинг: 0 / 0

INSERT INTO ... SELECT медленная вставка

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

авторПосле вставки индекса все стало хужеможно попробовать set enable_nestloop to off;

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 16:11

#35936586

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Создал индекс. Выключил вложеные циклы и создал ограничения

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.

"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"

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 16:18

#35936607

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Без PrimaryKey и unique вставка теперь 3.5 секунды.
Кстати в этой таблице (куда вставляем) впоследствии будет данных порядка 400-500 млн причем добавляться будет по 1 млн в среднем в неделю. Если я оставлю эту таблицу без Primary Key. Как то скажется скорость выборок на этом?

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 16:20

#35936615

Ёш

Участник

Сообщения: 2 921
Рейтинг: 0 / 0

INSERT INTO ... SELECT медленная вставка

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 выполнить:

Код: plaintext

1.
2.

--- собираем статистику распределения данных в Вашей временной таблице
--- для планировщика запросов
analyze my_table1;

мне кажется что 200 тысяч строк и их использование во нескольких join'ах уже требуют нормальной статистики для оптимального планирования запроса.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 16:21

#35936619

assa

Участник

Откуда: мы сами не местные
Сообщения: 3 467
Рейтинг: 0 / 0

INSERT INTO ... SELECT медленная вставка

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

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

Когда-то давно просматривал вариант сноса индексов-ограничений до вставки и поднятия после - не понравилось. и дороже, и ф-ии в том же сеансе ошибались (на oid-ах индексов).

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 16:32

#35936656

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Код: plaintext

analyze my_table1

Для временной таблицы делается

По поводу ключей. Я пока пытаюсь еще только спрогнозировать как будет работать база, а посколько опыта конкретно с Postgres не имею, то и мечусь. Вопрос про нужность Primary key все еще актуален.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 16:40

#35936675

LeXa NalBat

Участник

Откуда: Москва
Сообщения: 2 843
Рейтинг: 0 / 0

INSERT INTO ... SELECT медленная вставка

DasterЕсли я оставлю эту таблицу без Primary Key. Как то скажется скорость выборок на этом?зависит от выборок. для ускорения определённых выборок можно будет создать индекс (по тем же полям, что удалённый primary key).

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 16:42

#35936681

LeXa NalBat

Участник

Откуда: Москва
Сообщения: 2 843
Рейтинг: 0 / 0

INSERT INTO ... SELECT медленная вставка

DasterВот план без индексов

"Total runtime: 4135.964 ms"

Подскажите куда копать чтобы оптимизировать?косяков в плане не видно. может взглянуть на hdd, память, настройки постгреса?

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 17:08

#35936767

Warstone

Участник

Сообщения: 4 422
Рейтинг: 0 / 0

INSERT INTO ... SELECT медленная вставка

Индекс по sync (kind, other_id, oper_id) должен помочь. Так как таким образом 3 джойна будут работать быстрее.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

16.04.2009, 18:52

#35937131

Daster

Гость

INSERT INTO ... SELECT медленная вставка

Всем спасибо за участие (особенно LeXa NalBat - мне бы так научиться планы читать). Пока решил остановиться на варианте без Primary key и unique constraint для таблицы куда вставляются данные. Для таблицы sync (джойнится 3 раза к времянке) пока оставлю индекс по (other_id и oper_id).
Протестировал на времянке в 300 тыс. записей - время вставки 12 сек - меня такое время устраивает. А все остальные проблемы с выборками буду решать по мере поступления.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

Период между сообщениями больше года.

29.01.2020, 12:32

#39919882

IvanC

Гость

INSERT INTO ... SELECT медленная вставка

Пожалуй, подниму тему. Тот же самый INSERT INTO SELECT работает ну просто неприлично долго (10 секунд) при условии, что SELECT выполняется 300 мс, при этом CREATE AS SELECT выполняется столько же (300мс). Это сам запрос:

Код: plsql

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;

Тут происходит затык:

Код: plsql

1.
2.
3.
4.
5.

"                                ->  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"

Подскажите, пожалуйста, в чем может быть проблема?

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

29.01.2020, 14:17

#39919929

Troglodit

Гость

INSERT INTO ... SELECT медленная вставка

Вы хотите сказать, что для вставки 10 записей в таблицу 10с вина именно insert?
Если вы там в триггерах ничего не майните и диски не битые, нет хитрых проверок, то вряд ли. Скорее всего проблема в select.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

29.01.2020, 14:51

#39919958

IvanC

Гость

INSERT INTO ... SELECT медленная вставка

Но отдельно же он отрабатывает за 300 мс, таблица, в которую я вставляю UNLOGGED, с триггерами не игрался и диски, хоть и старые в RAIDе, но подозрений не вызывают(копирую по сети на них со скоростью 30 Мбайт/сек)... Вот и я уже голову сломал, почему так...

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

все

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&mobile=1&tid=1994844]:	0ms
get settings:	9ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	34ms
get topic data:	11ms
get forum data:	3ms
get page messages:	57ms
get tp. blocked users:	1ms
others:	13ms

total:	150ms