ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / рефакторинг большой таблицы. 2 вопроса.
11 сообщений из 11, страница 1 из 1
рефакторинг большой таблицы. 2 вопроса.
    #34752195
untitled
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
untitled
Участник
Всем привет.
Есть следующая проблема:
существует одна таблица, для простоты картины ее можно представить в таком виде:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE "values"
(
  val_id bigint NOT NULL,
  val_data text,
  ... тут еще много других полей
  CONSTRAINT pk_values PRIMARY KEY (val_id),
) 
WITHOUT OIDS;

В этой таблице ~ 50 млн. записей. Решено было сделать следующую оптимизацию - значения из val_data вынести в отдельную таблицу и заменить это поле внешним ключем. Для этого была создана таблица:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE "unique_values"
(
  uv_id bigint NOT NULL,
  uv_value text,
  CONSTRAINT pk_unique_values PRIMARY KEY (uv_id)
) 
WITHOUT OIDS;

а в исходную добавлена колонка uv_id.

после этого во вторую таблицу были вставлены все неповторяющиеся значения из поля val_data первой таблицы. Получилось в итоге примерно 10 млн. записей.


Вопрос первый:

Каким самым быстрым образом можно связать между собой эти две таблицы? То есть обновить в первой таблице поле uv_id?
Поясню в чем проблема:
запрос

Код: plaintext
update values set uv_id = (select uv_id from unique_values where uv_value=val_data)

выполняется безобразно долго и мучительно. если делать update частями, то запрос вида

Код: plaintext
update values set uv_id = (select uv_id from unique_values where uv_value=val_data) where  val_id>= 0 ::bigint and val_id< 10000 ::bigint;

выполняется ~1000 секунд. То есть 10 тысяч строк апдейтятся 16 минут. а строк, которые надо проапдейтить - 50 миллионов. с такими скоростями на это уйдет 55 суток ((

ps: индексы (btree, clustered) созданы на каждые поля таблицы

Вопрос второй:

для уменьшения времени поиска вторую таблицу я разбил на множество более мелких, используя партиционирование (примерно как тут: http://postgrestips.blogspot.com/2007/06/partitial-table.html), обвесив таблицу правилами наподобие

Код: plaintext
1.
2.
3.
4.
CREATE OR REPLACE RULE uv_rule_a AS
    ON INSERT TO unique_values
   WHERE new.uv_value >= 'a'::text AND new.uv_value < 'b'::text DO INSTEAD  INSERT INTO unique_value_a (uv_id, uv_value) 
  VALUES (new.uv_id, new.uv_value);

и так для всех букв алфавита, то есть создано множество таблиц, делящих текст по букве, с которой начинается текстовое поле. Соответственно идентификаторы uv_id так же оказались разнесены по разным таблицам. Проблема заключается в том, что uv_id является внешним ключем для таблицы values. а так как значения этого ключа оказались разнесенными по разным таблицам у меня не получается повесить constraint на таблицу values:

Код: plaintext
1.
2.
3.
4.
ALTER TABLE "values"
  ADD CONSTRAINT fk_values_reference_unique_v FOREIGN KEY (uv_id)
      REFERENCES unique_values (uv_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

этот код генерирует ошибку говорящую о том, что необходимые ключи не найдены в таблице unique_values. Можно каким-либо образом повесить constraint на таблицу values или придется оставить все как есть?
...
Рейтинг: 0 / 0
24.08.2007, 23:32
| Ответить | Цитировать | Написать  
рефакторинг большой таблицы. 2 вопроса.
    #34753779
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
untitledКаким самым быстрым образом можно связать между собой эти две таблицы? То есть обновить в первой таблице поле uv_id?Попробуйте update values set uv_id = unique_values.uv_id from unique_values where values.uv_value=unique_values.val_data. Покажите получившийся EXPLAIN, наверное надо добиться Merge Join.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
create table t1 (
 id serial primary key,
 name text
);

create table t2 (
 id serial primary key,
 name text
);

explain
 update t2 set name = ( select name from t1 where t1.id=t2.id );

explain
 update t2 set name = t1.name from t1 where t2.id=t1.id;

drop table t1;
drop table t2;
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
nalbat=> explain
nalbat->  update t2 set name = ( select name from t1 where t1.id=t2.id );
                                QUERY PLAN
---------------------------------------------------------------------------
 Seq Scan on t2  (cost= 0 . 00 .. 5616 . 31  rows= 1160  width= 10 )
   SubPlan
     ->  Index Scan using t1_pkey on t1  (cost= 0 . 00 .. 4 . 82  rows= 1  width= 32 )
           Index Cond: (id = $ 0 )
( 4  rows)

nalbat=> explain
nalbat->  update t2 set name = t1.name from t1 where t2.id=t1.id;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Merge Join  (cost= 0 . 00 .. 124 . 92  rows= 1160  width= 42 )
   Merge Cond: ("outer".id = "inner".id)
   ->  Index Scan using t2_pkey on t2  (cost= 0 . 00 .. 53 . 76  rows= 1160  width= 10 )
   ->  Index Scan using t1_pkey on t1  (cost= 0 . 00 .. 53 . 76  rows= 1160  width= 36 )
( 4  rows)

untitledПоясню в чем проблема: запрос
Код: plaintext
1.
update values set uv_id =
 (select uv_id from unique_values where uv_value=val_data)
выполняется безобразно долго и мучительно. если делать update частями, то запрос вида
Код: plaintext
1.
2.
update values set uv_id =
 (select uv_id from unique_values where uv_value=val_data)
 where  val_id>= 0 ::bigint and val_id< 10000 ::bigint;
выполняется ~1000 секунд.Для того чтобы понять порядок выполнения запросов смотрите EXPLAIN.
...
Рейтинг: 0 / 0
27.08.2007, 10:54
| Ответить | Цитировать | Написать  
рефакторинг большой таблицы. 2 вопроса.
    #34754271
untitled
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
untitled
Участник
Да, вариант с join быстрее на два порядка:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
explain update values set uv_id = a.uv_id from unique_values a where val_data=uv_value
----------------------------------------------------------------------------------------------------

Merge Join  (cost= 3199644 . 46 .. 350431000 . 85  rows= 22868087715  width= 73 )
  Merge Cond: (outer.val_data = inner.uv_value)
  ->  Index Scan using i_values_data on values  (cost= 0 . 00 .. 4097054 . 17  rows= 45194602  width= 65 )
  ->  Sort  (cost= 3199644 . 46 .. 3225610 . 68  rows= 10386489  width= 275 )
        Sort Key: a.uv_value
        ->  Append  (cost= 0 . 00 .. 275660 . 89  rows= 10386489  width= 275 )
              ->  Seq Scan on unique_values a  (cost= 0 . 00 .. 106611 . 95  rows= 1677195  width= 272 )
              ->  Seq Scan on unique_value_a a  (cost= 0 . 00 .. 3003 . 49  rows= 171649  width= 27 )
              ->  Seq Scan on unique_value_0 a  (cost= 0 . 00 .. 123 . 29  rows= 7129  width= 24 )
              ->  Seq Scan on unique_value_b a  (cost= 0 . 00 .. 2087 . 63  rows= 119863  width= 26 )
              ->  Seq Scan on unique_value_c a  (cost= 0 . 00 .. 1996 . 38  rows= 114338  width= 27 )
              ->  Seq Scan on unique_value_d a  (cost= 0 . 00 .. 2059 . 84  rows= 119184  width= 25 )
              ->  Seq Scan on unique_value_e a  (cost= 0 . 00 .. 1117 . 76  rows= 64176  width= 26 )
              ->  Seq Scan on unique_value_f a  (cost= 0 . 00 .. 1345 . 78  rows= 77478  width= 26 )
              ->  Seq Scan on unique_value_g a  (cost= 0 . 00 .. 1326 . 42  rows= 76742  width= 26 )
              ->  Seq Scan on unique_value_h a  (cost= 0 . 00 .. 58384 . 13  rows= 2941613  width= 46 )
              ... здесь еще две сотни подобных строк

против

Код: 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.
update values set uv_id = (select uv_id from unique_values where uv_value=val_data) 

----------------------------------------------------------------------------------------------------

Seq Scan on values  (cost= 0 . 00 .. 17616211951 . 48  rows= 45216769  width= 65 )
  SubPlan
    ->  Result  (cost= 0 . 00 .. 389 . 57  rows= 127  width= 8 )
          ->  Append  (cost= 0 . 00 .. 389 . 57  rows= 127  width= 8 )
                ->  Index Scan using uv_value on unique_values  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 8 )
                      Index Cond: (uv_value = $ 0 )
                ->  Index Scan using uv_a_value on unique_value_a unique_values  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 8 )
                      Index Cond: (uv_value = $ 0 )
                ->  Index Scan using uv_0_value on unique_value_0 unique_values  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 8 )
                      Index Cond: (uv_value = $ 0 )
                ->  Index Scan using uv_b_value on unique_value_b unique_values  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 8 )
                      Index Cond: (uv_value = $ 0 )
                ->  Index Scan using uv_c_value on unique_value_c unique_values  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 8 )
                      Index Cond: (uv_value = $ 0 )
                ->  Index Scan using uv_d_value on unique_value_d unique_values  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 8 )
                      Index Cond: (uv_value = $ 0 )
                ->  Index Scan using uv_e_value on unique_value_e unique_values  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 8 )
                      Index Cond: (uv_value = $ 0 )
                ->  Index Scan using uv_f_value on unique_value_f unique_values  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 8 )
                      Index Cond: (uv_value = $ 0 )
                ->  Index Scan using uv_g_value on unique_value_g unique_values  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 8 )
                      Index Cond: (uv_value = $ 0 )
                ->  Index Scan using uv_h_value on unique_value_h unique_values  (cost= 0 . 00 .. 3 . 01  rows= 1  width= 8 )
                      Index Cond: (uv_value = $ 0 )
                ....

Однако общая стоимость запроса по прежнему не радует.
Сейчас пока обновляю таблицу кусками при помощи такого запроса и думаю что можно сделать еще...
Код: 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.
update values set uv_id = a.uv_id from unique_values a where val_data=uv_value and val_id in (select val_id from values where uv_id is null limit  200000 )
----------------------------------------------------------------------------------------------------

Hash Join  (cost= 11670 . 31 .. 340275 . 62  rows= 101198  width= 73 )
  Hash Cond: (outer.uv_value = inner.val_data)
  ->  Append  (cost= 0 . 00 .. 275660 . 89  rows= 10386489  width= 275 )
        ->  Seq Scan on unique_values a  (cost= 0 . 00 .. 106611 . 95  rows= 1677195  width= 272 )
        ->  Seq Scan on unique_value_a a  (cost= 0 . 00 .. 3003 . 49  rows= 171649  width= 27 )
        ->  Seq Scan on unique_value_0 a  (cost= 0 . 00 .. 123 . 29  rows= 7129  width= 24 )
        ->  Seq Scan on unique_value_b a  (cost= 0 . 00 .. 2087 . 63  rows= 119863  width= 26 )
        ->  Seq Scan on unique_value_c a  (cost= 0 . 00 .. 1996 . 38  rows= 114338  width= 27 )
        ->  Seq Scan on unique_value_d a  (cost= 0 . 00 .. 2059 . 84  rows= 119184  width= 25 )
        ->  Seq Scan on unique_value_e a  (cost= 0 . 00 .. 1117 . 76  rows= 64176  width= 26 )
        ->  Seq Scan on unique_value_f a  (cost= 0 . 00 .. 1345 . 78  rows= 77478  width= 26 )
        ->  Seq Scan on unique_value_g a  (cost= 0 . 00 .. 1326 . 42  rows= 76742  width= 26 )
        ->  Seq Scan on unique_value_h a  (cost= 0 . 00 .. 58384 . 13  rows= 2941613  width= 46 )
	...
  ->  Hash  (cost= 11669 . 81 .. 11669 . 81  rows= 200  width= 65 )
        ->  Nested Loop  (cost= 10474 . 90 .. 11669 . 81  rows= 200  width= 65 )
              ->  HashAggregate  (cost= 10474 . 90 .. 10476 . 90  rows= 200  width= 8 )
                    ->  Limit  (cost= 0 . 00 .. 7974 . 90  rows= 200000  width= 8 )
                          ->  Seq Scan on values  (cost= 0 . 00 .. 1250091 . 17  rows= 31350655  width= 8 )
                                Filter: (uv_id IS NULL)
              ->  Index Scan using i_values_id on values  (cost= 0 . 00 .. 5 . 95  rows= 1  width= 65 )
                    Index Cond: (values.val_id = outer.val_id)

реальное время выполнения каждого такого апдейта ~4500000 ms
...
Рейтинг: 0 / 0
27.08.2007, 12:38
| Ответить | Цитировать | Написать  
рефакторинг большой таблицы. 2 вопроса.
    #34754395
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
untitledОднако общая стоимость запроса по прежнему не радует.Merge Join не получился. Попробуйте запросами по частичным таблицам типа "update values set uv_id = a.uv_id from unique_values_a a where val_data=uv_value".

untitled... limit 200000. реальное время выполнения каждого такого апдейта ~4500000 msПри этом, как видно из плана, limit накладывается на values, а таблицы unique_values_* прочитываются целиком. Получается неэффективно. Для сравнения попробуйте "limit 400000", думаю что время выполнения возрастет меньше, чем в два раза.
...
Рейтинг: 0 / 0
27.08.2007, 13:02
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
рефакторинг большой таблицы. 2 вопроса.
    #35668155
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
untitledВсем привет.
Есть следующая проблема:
существует одна таблица, для простоты картины ее можно представить в таком виде:

В этой таблице ~ 50 млн. записей. Решено было сделать следующую оптимизацию - значения из val_data вынести в отдельную таблицу и заменить это поле внешним ключем. Для этого была создана таблица:

Получилось в итоге примерно 10 млн. записей. а ради чего это делалось?
применительно к оптимизации postgresql встречал рекомендацию наоборот делать всё одной таблицей с кучей полей, пусть даже и с некоторым дублированием информации.
логика: для доступа к данным придётся делать два сика вместо одного, накладные расходы на организацию второнй таблицы "съедят" экономию от удаления избыточных данных.

конечно не стоит доходить до крайностей - в данном конкретном случае (10 миллионов уникальных значений на 50 миллионов данных) вполне возможно, что нормализация была лишней.

выполняется ~1000 секунд. То есть 10 тысяч строк апдейтятся 16 минут. а строк, которые надо проапдейтить - 50 миллионов. с такими скоростями на это уйдет 55 суток ((

ps: индексы (btree, clustered) созданы на каждые поля таблицы к слову - чем меньше индексов - тем быстрее идёт обновление. afaik postgesql до недавнего времени перестраивал все индесы на каждый update (независимо от того, поменял ли update поля, участвующие в индексе или нет). хотя в данном конкретном случае дело явно не в индексах.
...
Рейтинг: 0 / 0
21.11.2008, 14:14
| Ответить | Цитировать | Написать  
рефакторинг большой таблицы. 2 вопроса.
    #35668328
untitled
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
untitled
Участник
eddie,

это было полтора года назад)
применительно к оптимизации postgresql встречал рекомендацию наоборот делать всё одной таблицей с кучей полей, пусть даже и с некоторым дублированием информации.
логика: для доступа к данным придётся делать два сика вместо одного, накладные расходы на организацию второнй таблицы "съедят" экономию от удаления избыточных данных.

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

если уж на то пошло прокомментирую второй свой вопрос:
делать партиционирование в постгре в виде
Код: plaintext
1.
2.
3.
CREATE OR REPLACE RULE uv_rule_a AS
    ON INSERT TO unique_values
   WHERE new.uv_value >= 'a'::text AND new.uv_value < 'b'::text DO INSTEAD  INSERT INTO unique_value_a (uv_id, uv_value) 
  VALUES (new.uv_id, new.uv_value);

- это крайне неудачное решение. все дело в том, что если потом сделать запрос вида
Код: plaintext
select * from unique_values where uv_value = 'abc'
то оно будет искать отнюдь не только в таблице unique_value_a, как хотелось бы, а вообще во всех таблицах. по крайней мере так было на тот момент, сейчас может уже и появился механизм, позволяющий партиционировать по значению текстовых полей, но не уверен.

вообще после различных экспериментов целесообразность партиционирования таблиц размером меньше 10 гигов (без учета размера индексов) и 100М записей под большим вопросом. если таблица общего назначения, то есть в нее и заносятся данные, и делаются выборки по различным критериям, то, наверное, самое лучшее - это разбить по первичному ключу, положив каждую из полученных таблиц в таблспейс на отдельный диск.
и еще - делать кластеризацию индекса на постоянно обновляемой таблице такого размера - нереально. так как CLUSTER делает полную блокировку таблицы, в том числе и для чтения, и занимает дофига времени. правда, как вариант, если позволяет логика программы, можно поочереди отключать таблицы,меняя правила, кластеризовывать их, а потом подключать обратно. но это далеко не везде применимо.
...
Рейтинг: 0 / 0
21.11.2008, 14:58
| Ответить | Цитировать | Написать  
рефакторинг большой таблицы. 2 вопроса.
    #35668521
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
untitlededdie,

это было полтора года назад) упс, это меня или форум переглючило? ;)

если уж на то пошло прокомментирую второй свой вопрос:
делать партиционирование в постгре в виде
Код: plaintext
1.
2.
3.
CREATE OR REPLACE RULE uv_rule_a AS
    ON INSERT TO unique_values
   WHERE new.uv_value >= 'a'::text AND new.uv_value < 'b'::text DO INSTEAD  INSERT INTO unique_value_a (uv_id, uv_value) 
  VALUES (new.uv_id, new.uv_value);

- это крайне неудачное решение. все дело в том, что если потом сделать запрос вида
Код: plaintext
select * from unique_values where uv_value = 'abc'
то оно будет искать отнюдь не только в таблице unique_value_a, как хотелось бы, а вообще во всех таблицах. по крайней мере так было на тот момент, сейчас может уже и появился механизм, позволяющий партиционировать по значению текстовых полей, но не уверен. случаем не забыли включить constraint_exclusion и на дочерних таблицах сделать "CHECK (uv_value >= 'a'::text AND uv_value < 'b'::text)"?
...
Рейтинг: 0 / 0
21.11.2008, 15:42
| Ответить | Цитировать | Написать  
рефакторинг большой таблицы. 2 вопроса.
    #35668549
untitled
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
untitled
Участник
случаем не забыли включить constraint_exclusion и на дочерних таблицах сделать "CHECK (uv_value >= 'a'::text AND uv_value < 'b'::text)"?
нет конечно, не забыл.
...
Рейтинг: 0 / 0
21.11.2008, 15:49
| Ответить | Цитировать | Написать  
рефакторинг большой таблицы. 2 вопроса.
    #35669135
eddie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
eddie
Участник
имхо это уже повод написать багрепорт
...
Рейтинг: 0 / 0
21.11.2008, 20:50
| Ответить | Цитировать | Написать  
рефакторинг большой таблицы. 2 вопроса.
    #35669157
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
untitled
вообще после различных экспериментов целесообразность партиционирования таблиц размером меньше 10 гигов (без учета размера индексов) и 100М записей под большим вопросом. если таблица общего назначения, то есть в нее и заносятся данные, и делаются выборки по различным критериям, то, наверное, самое лучшее - это разбить по первичному ключу, положив каждую из полученных таблиц в таблспейс на отдельный диск.

Постгрес в продакшене использовался задолго до того, как стали доступны гигабайты памяти и быстрые диски. Например, на машинке с 256 мб ОЗУ и аташным винтом гиг на 40 указанные вами объемы данных без партиционирования обрабатывать нереально.

P.S. Первый свой сервак с постгресом я делал на пентиум I, разогнанном до 166 МГц, с 32Мб ОЗУ. А теперь на машинках с 32Гб ОЗУ часто слышим о проблемах с базами размером в проценты от доступной памяти :-) Это я к тому, что эффективность любой техники оптимизации зависит от вида и стат. распределения хранимых данных, интенсивности и типа нагрузки и нельзя для произвольной базы на неизвестном железе однозначно сказать, что такое хорошо и что такое плохо. Пробовать надо - именно с этими данными и на этом железе.
...
Рейтинг: 0 / 0
21.11.2008, 21:16
| Ответить | Цитировать | Написать  
рефакторинг большой таблицы. 2 вопроса.
    #35669384
untitled
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
untitled
Участник
да я в принципе всё это понимаю, это я так, усредненно к современным реалиям привел)
...
Рейтинг: 0 / 0
22.11.2008, 04:40
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / рефакторинг большой таблицы. 2 вопроса.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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