ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Вставка данных во вложенные таблицы
19 сообщений из 19, страница 1 из 1
Вставка данных во вложенные таблицы
    #34935774
Casufi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Casufi
Участник
Подскажите пожалуйста как красивее это сделать.
Классика жанра, есть две таблицы, поле в одной является автоинкрементным уникальным ключем для другой.
Если нам нужно просто вставить строку в две таблицы, то значение ключа получаем при помощи curval, проблемі начинаются в том случае, если во второй таблице уже существует запись, и все что нам нужно сделать , это найти ID поля и вставить его.
Если мы сначала делаем SELECT, и только потом, в случае необходимости INSERT, эта процедура сильно замедляет процесс вставки строк в таблицу.
Для того, чтобы избежать лишних SELECTов я пишу так:
BEGIN
INSERT INTO table_1 (value) VALUES (buf.sumvalue);
variableforid = currval(table_1_id_seq);
EXCEPTION
WHEN unique_violation THEN
SELECT INTO variableforid id from table_1 WHERE value = buf.sumvalue;
END;

Далее инсерт в зависимую таблицу.

Но вот со временем и этот select тоже начинает притормаживать выполнение функции.
Существует ли более красивый способ ?
...
Рейтинг: 0 / 0
13.11.2007, 15:39
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34935842
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
CasufiЕсли мы сначала делаем SELECT, и только потом, в случае необходимости INSERT, эта процедура сильно замедляет процесс вставки строк в таблицу."сильно замедляет"? вы уверены, что этот select намного медленнее двух insert-ов?

CasufiСуществует ли более красивый способ ?обсуждали похожий вопрос: insert or update . мне в голову более красивого способа не приходит.
...
Рейтинг: 0 / 0
13.11.2007, 15:53
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34935968
Casufi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Casufi
Участник
LeXa NalBat"сильно замедляет"? вы уверены, что этот select намного медленнее двух insert-ов?
Проверил, время выполнения при пустых таблицах (выполняются чистые инсерты) в два раза больше времени выполнения если вторая таблица заполненна. А если учесть, что в процессе выполнения функции, объем данных по которым нужно сделать SELECT WHERE растет, а время выполнения INSERT остается постоянным
...
Рейтинг: 0 / 0
13.11.2007, 16:27
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34935999
Casufi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Casufi
Участник
Casufi LeXa NalBat"сильно замедляет"? вы уверены, что этот select намного медленнее двух insert-ов?
Проверил, время выполнения при пустых таблицах (выполняются чистые инсерты) в два раза больше времени выполнения если вторая таблица заполненна. А если учесть, что в процессе выполнения функции, объем данных по которым нужно сделать SELECT WHERE растет, а время выполнения INSERT остается постоянным
Опечатался, время выполнения на чистых инсертах в два раза меньше!
...
Рейтинг: 0 / 0
13.11.2007, 16:33
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34936127
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
CasufiПроверил, время выполнения при пустых таблицах (выполняются чистые инсерты) в два раза больше времени выполнения если вторая таблица заполненна.я тоже проверил. попытался сравнить скорости select, insert, неудачный insert. select выполняется в два раза быстрее insert-а, время неудачного insert-а получить не удалось.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
create table t1 ( id integer primary key );

insert into t1 select generate_series( 1 , 100000 , 2 );
vacuum analyze;

explain analyze select * from t1 where id= 66666 ;
explain analyze select * from t1 where id between  70000  and  80000 ;

explain analyze insert into t1 values ( 33334 );
explain analyze insert into t1 select generate_series( 80000 , 90000 , 2 );

explain analyze insert into t1 values ( 33334 );
explain analyze insert into t1 select generate_series( 80000 , 90000 , 2 );

drop table t1;
Код: 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.
30.
31.
32.
33.
nalbat=> explain analyze select * from t1 where id= 66666 ;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Index Scan using t1_pkey on t1  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 4 ) (actual time= 0 . 012 .. 0 . 012  rows= 0  loops= 1 )
   Index Cond: (id =  66666 )
 Total runtime:  0 . 043  ms
( 3  rows)

nalbat=> explain analyze select * from t1 where id between  70000  and  80000 ;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Scan using t1_pkey on t1  (cost= 0 . 00 .. 126 . 12  rows= 4945  width= 4 ) (actual time= 0 . 017 .. 17 . 145  rows= 5000  loops= 1 )
   Index Cond: ((id >=  70000 ) AND (id <=  80000 ))
 Total runtime:  27 . 647  ms
( 3  rows)

nalbat=> explain analyze insert into t1 values ( 33334 );
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost= 0 . 00 .. 0 . 01  rows= 1  width= 0 ) (actual time= 0 . 003 .. 0 . 005  rows= 1  loops= 1 )
 Total runtime:  0 . 071  ms
( 2  rows)

nalbat=> explain analyze insert into t1 select generate_series( 80000 , 90000 , 2 );
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Result  (cost= 0 . 00 .. 0 . 01  rows= 1  width= 0 ) (actual time= 0 . 008 .. 12 . 481  rows= 5001  loops= 1 )
 Total runtime:  70 . 630  ms
( 2  rows)

nalbat=> explain analyze insert into t1 values ( 33334 );
ERROR:  повторный ключ нарушает констрейнт UNIQUE "t1_pkey"
nalbat=> explain analyze insert into t1 select generate_series( 80000 , 90000 , 2 );
ERROR:  повторный ключ нарушает констрейнт UNIQUE "t1_pkey"

CasufiА если учесть, что в процессе выполнения функции, объем данных по которым нужно сделать SELECT WHERE растет, а время выполнения INSERT остается постояннымпочему у вас так происходит? select и insert должны перелопачивать одинаковый объем данных по уникальному индексу.
...
Рейтинг: 0 / 0
13.11.2007, 17:00
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34936250
Casufi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Casufi
Участник
LeXa NalBatпочему у вас так происходит? select и insert должны перелопачивать одинаковый объем данных по уникальному индексу.
Я не очень давно пробую писать для Postgre, поєтому толком профилировать функции не умею, и не могу сказать почему так происходит.

Подскажите, можно использовать "explain analyze" в теле функции?
Нашел еще одну интересную особенность, есть функция "insert_dvizh" которая обрабатывает одну строку из буферной таблицы, скорость обработки по идее должна немного увеличиваться по мере наполнения таблицы данными, а не от количества строк в запросе.
Но почемуто 10 запросов SELECT id, insert_dvizh(id) from buffer limit 1000 выполняются в 4-6 раз быстрее чем один запрос SELECT id, insert_dvizh(id) from buffer limit 10000
...
Рейтинг: 0 / 0
13.11.2007, 17:32
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34936281
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
Casufi
Но почемуто 10 запросов SELECT id, insert_dvizh(id) from buffer limit 1000 выполняются в 4-6 раз быстрее чем один запрос SELECT id, insert_dvizh(id) from buffer limit 10000
Расходы на транзакцию во втором случае больше.
Транзакция изолирует от остального мира бОльший объем данных на бОльшее время.
...
Рейтинг: 0 / 0
13.11.2007, 17:40
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34936304
Casufi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Casufi
Участник
tadminРасходы на транзакцию во втором случае больше.
Транзакция изолирует от остального мира бОльший объем данных на бОльшее время.
Грубо говоря, если база будет крутиться на сервере с лостаточным количеством оперативки а не на рабочей станции, то время обработки должно постепенно сравниваться ?
...
Рейтинг: 0 / 0
13.11.2007, 17:47
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34936318
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
CasufiПодскажите, можно использовать "explain analyze" в теле функции? Вот как можно план изнутри функции просмотреть

CasufiНо почемуто 10 запросов SELECT id, insert_dvizh(id) from buffer limit 1000 выполняются в 4-6 раз быстрее чем один запрос SELECT id, insert_dvizh(id) from buffer limit 10000в обоих случаях в таблицы успешно добавляется 10 000 строк?

PS: вы используете limit без order by
...
Рейтинг: 0 / 0
13.11.2007, 17:49
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34936361
Casufi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Casufi
Участник
tadmin Casufi
Но почемуто 10 запросов SELECT id, insert_dvizh(id) from buffer limit 1000 выполняются в 4-6 раз быстрее чем один запрос SELECT id, insert_dvizh(id) from buffer limit 10000
Расходы на транзакцию во втором случае больше.
Транзакция изолирует от остального мира бОльший объем данных на бОльшее время.
КРоме этого, возможно ли выполнить запрос так, чтобы расчеты по каждой строке, которая возвращается в этом запросе выполнялись в отдельной транзакции ?
...
Рейтинг: 0 / 0
13.11.2007, 17:59
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34937298
Casufi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Casufi
Участник
LeXa NalBatв обоих случаях в таблицы успешно добавляется 10 000 строк?

PS: вы используете limit без order by
В оригинальном запросе LIMIT используется вместе с ORDER BY
Сама функция вставляет данные в 7 таблиц. Результат выполнения 1 селекта на 1000 строк и 10 на 100 абсолютно одинаковый.
...
Рейтинг: 0 / 0
14.11.2007, 09:55
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34937707
Dan Black
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dan Black
Участник
пожалуй, придерусь к фразе (из альтруистических соображений), хотя и небольшой оффтопик
Casufi[Результат выполнения 1 селекта на 1000 строк и 10 на 100 абсолютно одинаковый.
В общем случае это неверно даже с использованием ORDER BY , так как любая завершившаяся транзакция (можду этими 10-тью вызовами select ... limit 1000 ), которая изменит таблицу buffer, может повлиять на следущий select ... limit 1000 таким образом, что целостность данных при этом будет потеряна, то есть
Код: plaintext
 10  x SELECT * FROM buffer ORDER BY field ASC LIMIT  1000  <> SELECT * FROM buffer ORDER BY field LIMIT  10000
...
Рейтинг: 0 / 0
14.11.2007, 11:32
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34937775
Gold_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gold_
Гость
LeXa NalBat Вот как можно план изнутри функции просмотреть


и уже в ссылке:

LeXa NalBatНе получается посмотреть таким способом план запроса с параметрами.


Так можно (если можно, то как?) посмотреть план запроса с параметрами?
...
Рейтинг: 0 / 0
14.11.2007, 11:47
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34937971
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Casufiвозможно ли выполнить запрос так, чтобы расчеты по каждой строке, которая возвращается в этом запросе выполнялись в отдельной транзакции ?наверное нельзя

CasufiНо почемуто 10 запросов SELECT id, insert_dvizh(id) from buffer limit 1000 выполняются в 4-6 раз быстрее чем один запрос SELECT id, insert_dvizh(id) from buffer limit 10000в обоих случаях в таблицы успешно добавляется 10 000 строк?

Gold_Так можно (если можно, то как?) посмотреть план запроса с параметрами? получилось посмотреть explain без analyze
...
Рейтинг: 0 / 0
14.11.2007, 12:24
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34938026
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
Casufi tadminРасходы на транзакцию во втором случае больше.
Транзакция изолирует от остального мира бОльший объем данных на бОльшее время.
Грубо говоря, если база будет крутиться на сервере с лостаточным количеством оперативки а не на рабочей станции, то время обработки должно постепенно сравниваться ?
теоретически - да.
На практике размер транзакции сложно влияет на скорость исполнения и зависит многих факторов:
1) помещается ли размер транзакции в ОЗУ?
2) помещается ли размер транзакции в кеш процессора (1-2-6 мб)?
3) нет ли плохого кода в самом постгресе, который приводит к скорости исполнения порядка O(N^2)?

На обычных задачах, ограничением является п 2). Как только транзакция (изоляция индексов и таблиц) начинается вытеснятся из кеша процессора, тут же появляется зависимость хуже чем O(N)

На моей практике (импорт больших объемов данных со сложными вставками во множество таблиц),
есть оптимальный размер транзакции. При переходе с 7ки на 8ку на одном железе оптимальный размерм транзакции вместо 20 записей стал 50. При переходе со старого Xeon на Opteron 852 опт. размер стал 80-90.
...
Рейтинг: 0 / 0
14.11.2007, 12:37
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34938554
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
Предлагаю в ФАК:

Вопрос: Правда ли что в PostgreSQL скорость выполнения транзакции (множество операций INSERT) не зависит от размеров транзакции?

Ответ (с)tadmin:
На практике размер транзакции сложно влияет на скорость исполнения и зависит многих факторов:
1) помещается ли размер транзакции в ОЗУ?
2) помещается ли размер транзакции в кеш процессора (1-2-6 мб)?
3) нет ли плохого кода в самом постгресе, который приводит к скорости исполнения порядка O(N^2)?

На обычных задачах, ограничением является п 2). Как только транзакция (изоляция индексов и таблиц) начинается вытеснятся из кеша процессора, тут же появляется зависимость хуже чем O(N)

На моей практике (импорт больших объемов данных со сложными вставками во множество таблиц),
есть оптимальный размер транзакции. При переходе с 7ки на 8ку на одном железе оптимальный размерм транзакции вместо 20 записей стал 50. При переходе со старого Xeon на Opteron 852 опт. размер стал 80-90.
...
Рейтинг: 0 / 0
14.11.2007, 14:09
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34938756
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
нашел свои старые измерения.
Импорт 15тыс записей, которые набор из 5-6 хранимых процедур plpgsql распихивает в 4-5 таблиц.

Измерения проводились на машине
dual Xeon 2.4, 2Gb RAM 5x 15K rpm RAID5
Импорт 15К записей, зависимость от chunksize (число записей за 1 транзакцию)
98% загрузка на 1 процессор, <40.0 IOPS, postmaster memory ~40mb
В данном случае скорость лимитировалась только процессором.

Накладные расходы на одну транзакцию состоят из
C1 - постоянной части (не зависит от chunk size)
C2 - переменной части (зависит от chunk size).

В данном случае С1 сравнимо с С2 при chunksize=20 , где и виден оптимум.
C2 всегда растет с увеличением chunk size.

На более совершенных процессорах и/или больших размерах L2-L3 кеша минимум на графике смещается вправо.
...
Рейтинг: 0 / 0
14.11.2007, 14:40
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34939719
Casufi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Casufi
Участник
Dan Blackпожалуй, придерусь к фразе (из альтруистических соображений), хотя и небольшой оффтопик
Casufi[Результат выполнения 1 селекта на 1000 строк и 10 на 100 абсолютно одинаковый.
В общем случае это неверно даже с использованием ORDER BY , так как любая завершившаяся транзакция (можду этими 10-тью вызовами select ... limit 1000 ), которая изменит таблицу buffer, может повлиять на следущий select ... limit 1000 таким образом, что целостность данных при этом будет потеряна, то есть
Код: plaintext
 10  x SELECT * FROM buffer ORDER BY field ASC LIMIT  1000  <> SELECT * FROM buffer ORDER BY field LIMIT  10000


Во первых не SELECT *, а SELECT id_field, some_func(id_field) FROM table ORDER BY id_field
И только от содержимого функции будет зависет совпадет результат или нет.
В моем случае функция удаляет за собой строку из базы, поєтому два раза одна строка не обрабатывается
...
Рейтинг: 0 / 0
14.11.2007, 17:42
| Ответить | Цитировать | Написать  
Вставка данных во вложенные таблицы
    #34942221
Casufi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Casufi
Участник
Итак, получается, что весь запрос SELECT id_field, some_func(id_field) FROM table ORDER BY id_field выполняется в одной транзакции, поэтому производительность данной операции сильно падает.

Подскажите пожалуйста, можно ли сделать так, чтобы каждая строка этого запроса выполнялась в отдельной транзакции ?
...
Рейтинг: 0 / 0
15.11.2007, 14:36
| Ответить | Цитировать | Написать  
19 сообщений из 19, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Вставка данных во вложенные таблицы
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]