ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Зависимость скорости Update от соседних индексов.
5 сообщений из 5, страница 1 из 1
Зависимость скорости Update от соседних индексов.
    #35250372
Vasonik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vasonik
Гость
Имеем таблицу с 1144290 записей

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE "public"."reply_data" (
  "rfd_id" SMALLINT NOT NULL, 
  "field_id" INTEGER NOT NULL, 
  "rvalue" VARCHAR( 4096 ), 
  "rses_id" INTEGER
) WITHOUT OIDS;

CREATE INDEX "field_id_idx" ON "public"."reply_data"
  USING btree ("field_id");

CREATE INDEX "rfd_id_idx" ON "public"."reply_data"
  USING btree ("rfd_id");

При наличие 2 индексов в таблице время запроса

Код: plaintext
update reply_data set rses_id =  1  where rses_id isnull
15 мин

если индексы убрать
время выполнения 1 мин

Почему время выполнения может Update зависит от индексов по полям, которые никак в запросе не участвуют?

Никаких триггеров и FK нету. Сервер 8.3. настройки postgresql.conf стандартные.
...
Рейтинг: 0 / 0
11.04.2008, 16:30
| Ответить | Цитировать | Написать  
Зависимость скорости Update от соседних индексов.
    #35250670
st_serg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
st_serg
Участник
небольшой тест:
Код: 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.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
postgres=# \d tab1
      Таблица "public.tab1"
 Колонка |   Тип   | Модификаторы
---------+---------+--------------
 f1      | integer |
 f2      | integer |
 f3      | integer |
Индексы:
    "tab1_i_f1" btree (f1)
    "tab1_i_f2" btree (f2)

postgres=# select count( 1 ) from tab1;
 count
--------
  458752 
( 1  запись)

postgres=# vacuum full analyze tab1;
VACUUM
postgres=# select pg_total_relation_size('tab1');
 pg_total_relation_size
------------------------
                40976384 
( 1  запись)

postgres=# select * from pgstattuple('tab1');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
   20316160  |       458752  |   16515072  |          81 . 29  |                 0  |               0  |                   0  |       61440  |           0 . 3 
( 1  запись)

Время:  220 , 000  мс
postgres=# select * from pgstatindex('tab1_i_f1');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation

---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------
-
        2  |           2  |    10321920  |            290  |               5  |        1254  |            0  |              0  |             90 . 05  |                   0 
( 1  запись)

Время:  0 , 000  мс
postgres=# select * from pgstatindex('tab1_i_f2');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation

---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------
-
        2  |           2  |    10321920  |            290  |               5  |        1254  |            0  |              0  |             90 . 05  |                   0 
( 1  запись)

postgres=# update tab1 set f3 =  12345 ;
UPDATE  458752 
postgres=# analyze tab1;
ANALYZE
postgres=# select pg_total_relation_size('tab1');
 pg_total_relation_size
------------------------
                87539712 
( 1  запись)

postgres=# select * from pgstattuple('tab1');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
   40632320  |       458752  |   16515072  |          40 . 65  |            390857  |        14070852  |               34 . 63  |     2839064  |          6 . 99 
( 1  запись)

postgres=# select * from pgstatindex('tab1_i_f1');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation

---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------
-
        2  |           2  |    23486464  |            290  |               9  |        2857  |            0  |              0  |             79 . 08  |               51 . 63 
( 1  запись)

postgres=# select * from pgstatindex('tab1_i_f2');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation

---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------
-
        2  |           2  |    23404544  |            290  |               9  |        2847  |            0  |              0  |             79 . 36  |               51 . 81 
( 1  запись)
как видно после апдейта число "мертвых" строк таблицы увеличилось (0 -> 390857), размер индексов вырос примерно в два раза (szie: 10321920 -> 23486464, leaf_pages: 1254 -> 2857), полный размер таблицы тоже вырос примерно в два раза. Видимо достаточно много времени уходит на перестройку индексов. Насколько я знаю update в пг - это фактически delete + insert, т.е. апдейт по всей таблице удваивает число записей в ней (половина живые, половина мертвые), данные в индексах ведут себя аналогично. Таким образом, даже если вы не затрагиваете своим update'ом проиндексированные поля, индексы все равно изменяются.

ps в момент теста пг ругался в логах такими словами
Код: plaintext
1.
2.
LOG:  контрольные точки происходят слишком часто (секунд от друга:  12 )
ПОДСКАЗКА:  Подумайте об увеличении параметра конфигурации "checkpoint_segments".
...
Рейтинг: 0 / 0
11.04.2008, 17:50
| Ответить | Цитировать | Написать  
Зависимость скорости Update от соседних индексов.
    #35251610
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Можно поиграться с параметром fillfactor (ALTER TABLE ... SET STORAGE ())
...
Рейтинг: 0 / 0
12.04.2008, 22:40
| Ответить | Цитировать | Написать  
Зависимость скорости Update от соседних индексов.
    #35251624
Funny_Falcon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Funny_Falcon
Участник
Протестировал сейчас:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
create table test_tab1 (
  int1 integer,
  int2 integer,
  int3 integer
) with (fillfactor = XXX );

create index test_tab1_int1 on test_tab1 (int1) with (fillfactor= YY );
create index test_tab1_int2 on test_tab1 (int2) with (fillfactor= YY);

insert into test_tab1 (int1,int2,int3)
select i,  1000000 -i, i from generate_series( 1 ,  1000000 ) as i;

update test_tab1
set int3 = int3 +  1 ;
Получил (первая цифра - XXX, вторая - YYY, если умолчание - то WITH (...) не указывался)
(размеры смотрел в pgAdmin III):
Код: 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.
 100 , умолч
  До Update
    Таблица 38МБ
    Индексы 46МБ
  Update
    Время   31с
    Таблица 77МБ
    Индексы 109МБ (!)
 75 , умолч
  До Update
    Таблица 51МБ
    Индексы 46МБ
  Update
    Время   23с
    Таблица 85МБ
    Индексы 63МБ
 50 , умолч
  До Update
    Таблица 77МБ (!)
    Индексы 46МБ
  Update
    Время   10с (!!!)
    Таблица 77МБ (!)
    Индексы 46МБ (!!!!)
 75 ,  75 
  До Update
    Таблица 51МБ
    Индексы 50МБ
  Update
    Время   25с
    Таблица 85МБ
    Индексы 70МБ

Вывод: если ожидается много обновлений, то FILLFACTOR=50% - то что нужно.
Новая для 8.3 фишка (если не трогается индекс, то он не растёт при апдейте) работает в этом случае просто великолепно!!!
...
Рейтинг: 0 / 0
12.04.2008, 23:02
| Ответить | Цитировать | Написать  
Зависимость скорости Update от соседних индексов.
    #35251803
ChameLe0n
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ChameLe0n
Гость
Код: plaintext
Вывод: если ожидается много обновлений, то FILLFACTOR= 50 % - то что нужно.
А если 3-й раз проапдейтить?
PS у меня, где ожидается действительно много update fillfactor=10%
...
Рейтинг: 0 / 0
13.04.2008, 11:28
| Ответить | Цитировать | Написать  
5 сообщений из 5, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Зависимость скорости Update от соседних индексов.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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