Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Вставка данных во вложенные таблицы / 19 сообщений из 19, страница 1 из 1
13.11.2007, 15:39
    #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:53
    #34935842
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вставка данных во вложенные таблицы
CasufiЕсли мы сначала делаем SELECT, и только потом, в случае необходимости INSERT, эта процедура сильно замедляет процесс вставки строк в таблицу."сильно замедляет"? вы уверены, что этот select намного медленнее двух insert-ов?

CasufiСуществует ли более красивый способ ?обсуждали похожий вопрос: insert or update . мне в голову более красивого способа не приходит.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.11.2007, 16:27
    #34935968
Casufi
Casufi
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вставка данных во вложенные таблицы
LeXa NalBat"сильно замедляет"? вы уверены, что этот select намного медленнее двух insert-ов?
Проверил, время выполнения при пустых таблицах (выполняются чистые инсерты) в два раза больше времени выполнения если вторая таблица заполненна. А если учесть, что в процессе выполнения функции, объем данных по которым нужно сделать SELECT WHERE растет, а время выполнения INSERT остается постоянным
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.11.2007, 16:33
    #34935999
Casufi
Casufi
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вставка данных во вложенные таблицы
Casufi LeXa NalBat"сильно замедляет"? вы уверены, что этот select намного медленнее двух insert-ов?
Проверил, время выполнения при пустых таблицах (выполняются чистые инсерты) в два раза больше времени выполнения если вторая таблица заполненна. А если учесть, что в процессе выполнения функции, объем данных по которым нужно сделать SELECT WHERE растет, а время выполнения INSERT остается постоянным
Опечатался, время выполнения на чистых инсертах в два раза меньше!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.11.2007, 17:00
    #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:32
    #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:40
    #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:47
    #34936304
Casufi
Casufi
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вставка данных во вложенные таблицы
tadminРасходы на транзакцию во втором случае больше.
Транзакция изолирует от остального мира бОльший объем данных на бОльшее время.
Грубо говоря, если база будет крутиться на сервере с лостаточным количеством оперативки а не на рабочей станции, то время обработки должно постепенно сравниваться ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.11.2007, 17:49
    #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:59
    #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
| Ответить | Цитировать | Написать  
14.11.2007, 09:55
    #34937298
Casufi
Casufi
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вставка данных во вложенные таблицы
LeXa NalBatв обоих случаях в таблицы успешно добавляется 10 000 строк?

PS: вы используете limit без order by
В оригинальном запросе LIMIT используется вместе с ORDER BY
Сама функция вставляет данные в 7 таблиц. Результат выполнения 1 селекта на 1000 строк и 10 на 100 абсолютно одинаковый.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2007, 11:32
    #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:47
    #34937775
Gold_
Gold_
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вставка данных во вложенные таблицы
LeXa NalBat Вот как можно план изнутри функции просмотреть


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

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


Так можно (если можно, то как?) посмотреть план запроса с параметрами?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.11.2007, 12:24
    #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:37
    #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, 14:09
    #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:40
    #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, 17:42
    #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
| Ответить | Цитировать | Написать  
15.11.2007, 14:36
    #34942221
Casufi
Casufi
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вставка данных во вложенные таблицы
Итак, получается, что весь запрос SELECT id_field, some_func(id_field) FROM table ORDER BY id_field выполняется в одной транзакции, поэтому производительность данной операции сильно падает.

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


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