index vs. fullscan / IBM DB2, WebSphere, IMS, U2

ReSQL.ru

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

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / index vs. fullscan

17 сообщений из 17, страница 1 из 1

index vs. fullscan

#38601547

dsusr

Гость

Добрый день.

В тестовой базе 10.5.3 на SLES 11 SP1 есть строчная компрессная табличка, по которой выполняется запрос вида :

Код: sql

1.
2.
3.
4.
5.
6.
7.

SET CURRENT DEGREE = '8';

select "PERIOD", ANAL_T7, "D_C", COUNT(*), SUM("AMOUNT"), SUM("OTHER_AMT")
  FROM "DB2INST5"."SUN_ROW"
  WHERE PERIOD='2012001'
  GROUP BY "PERIOD", ANAL_T7, "D_C"
  ORDER BY "PERIOD", ANAL_T7, "D_C";

В таблице ~184 мил. записей, по указанному предикату отбирается ~ 950 тыс.

Без никаких индексов (фуллскан) выполняется за 31-32 сек.

Код: sql

1.
2.
3.
4.
5.
6.

* Summary Table:

Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
--------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- -------------
Statement           1           1       0.001007       0.001007       0.001007        0.001007       0.001007              0             0
Statement           2           1      32.137362      32.137362      32.137362       32.137362      32.137362            276           276

План :

Код: sql

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.

Access Plan:
-----------
        Total Cost:             386779
        Query Degree:           8

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
      1032
     GRPBY
     (   2)
     386779
     287579
       |
      1032
     LMTQ
     (   3)
     386779
     287579
       |
      1032
     TBSCAN
     (   4)
     386779
     287579
       |
      1032
     SORT
     (   5)
     386779
     287579
       |
      33602
     pGRPBY
     (   6)
     386769
     287579
       |
     394351
     TBSCAN
     (   7)
     386687
     287579
       |
   1.83908e+08
 TABLE: DB2INST5
     SUN_ROW
       Q1

Навешиваем индекс :

Код: sql

1.
2.
3.
4.

CREATE INDEX DB2INST5.IDX1_SUN_ROW ON DB2INST5.SUN_ROW
(PERIOD ASC)
COMPRESS YES
ALLOW REVERSE SCANS

Собираем статистику :

Код: sql

1.
2.
3.

runstats on table sun_row with distribution and detailed indexes all;

runstats on table sun_row with distribution ON ALL COLUMNS AND COLUMNS (PERIOD,ANAL_T7,D_C);

Перезапускаем запрос, получаем :

Код: sql

1.
2.
3.
4.
5.
6.

* Summary Table:

Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
--------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- -------------
Statement           1           1       0.001222       0.001222       0.001222        0.001222       0.001222              0             0
Statement           2           1      61.181024      61.181024      61.181024       61.181024      61.181024            276           276

План :

Код: sql

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.

Access Plan:
-----------
        Total Cost:             35452.6
        Query Degree:           8

            Rows
           RETURN
           (   1)
            Cost
             I/O
             |
            1032
           GRPBY
           (   2)
           35452.6
           30259.2
             |
            1032
           LMTQ
           (   3)
           35452.5
           30259.2
             |
            1032
           TBSCAN
           (   4)
           35452.4
           30259.2
             |
            1032
           SORT
           (   5)
           35452.4
           30259.2
             |
            33602
           pGRPBY
           (   6)
           35442.6
           30259.2
             |
           394351
           FETCH
           (   7)
           35360.2
           30259.2
         /---+----\
     394351     1.83908e+08
     IXSCAN   TABLE: DB2INST5
     (   8)       SUN_ROW
     142.591        Q1
     31.4604
       |
   1.83908e+08
 INDEX: DB2INST5
  IDX1_SUN_ROW
       Q1

Разница почти в два раза.

При создании индекса вида :

Код: sql

1.
2.
3.
4.
5.
6.

CREATE INDEX DB2INST5.IDX1_SUN_ROW ON DB2INST5.SUN_ROW
(PERIOD ASC,
 ANAL_T7 ASC,
 D_C ASC )
COMPRESS YES
ALLOW REVERSE SCANS

ситуация сильно не меняется (57-58) сек.

При тестовых запусках параллельной нагрузки нет. При плане по индексу идет wait i/o в 70-80%, при фулскане 10-15% во время выполнения запроса. Как бы заставить по индексу работать побыстрее ?

...

Рейтинг:

0 / 0

01.04.2014, 09:36

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

index vs. fullscan

#38601622

dsusr

Гость

Из снепшота выяснилось, что

при индексе :

Код: sql

1.
2.
3.

Total buffer pool read time (milliseconds) = 25845
Total buffer pool write time (milliseconds)= 0
Time waited for prefetch (ms)              = 405246

При фулскане :

Код: sql

1.
2.
3.

Total buffer pool read time (milliseconds) = 319
Total buffer pool write time (milliseconds)= 0
Time waited for prefetch (ms)              = 133420

Правда не очень понятно, как Time waited for prefetch может быть в разы больше чем весь Execute Time

...

Рейтинг:

0 / 0

01.04.2014, 10:44

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

index vs. fullscan

#38601644

Mark Barinstein

Гость

dsusr,

Добрый день.

Попробуйте:

Код: sql

1.
2.
3.

CREATE TABLE "DB2INST5"."SUN_ROW"
...
ORGANIZE BY (PERIOD)

Нужный индекс при этом сам создастся.

...

Рейтинг:

0 / 0

01.04.2014, 11:03

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

index vs. fullscan

#38601670

dsusr

Гость

Mark Barinstein,

MDC - это следуюйщий шаг :)
Хочется понять почему с "обычным" индексом идет просадка, должно же быть всяко быстрее фулскана

...

Рейтинг:

0 / 0

01.04.2014, 11:25

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

index vs. fullscan

#38601705

Mark Barinstein

Гость

dsusrХочется понять почему с "обычным" индексом идет просадка, должно же быть всяко быстрее фулсканаДанные могут быть не кластеризованы по индексу, оптимизатор неправильно оценивает стоимость IXSCAN-FETCH против TBSCAN.
Попробуйте

Код: sql

REORG TABLE "DB2INST5"."SUN_ROW" INDEX DB2INST5.IDX1_SUN_ROW

dsusrПравда не очень понятно, как Time waited for prefetch может быть в разы больше чем весь Execute TimeВремя ожидания суммируется по каждому из 8-ми параллельных агентов.

...

Рейтинг:

0 / 0

01.04.2014, 11:52

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

index vs. fullscan

#38602189

dsusr

Гость

Mark BarinsteinДанные могут быть не кластеризованы по индексу, оптимизатор неправильно оценивает стоимость IXSCAN-FETCH против TBSCAN.
Действительно, с кластерным индексом по PERIOD, а также после реорга с обычным индексом все становится веселее :

Код: sql

1.
2.
3.
4.
5.
6.

* Summary Table:

Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
--------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- -------------
Statement           1           1       0.000968       0.000968       0.000968        0.000968       0.000968              0             0
Statement           2           1       0.988999       0.988999       0.988999        0.988999       0.988999            276           276

Тот же запрос, но по колоночной таблице :

Код: sql

1.
2.
3.
4.
5.
6.

* Summary Table:

Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
--------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- -------------
Statement           1           1       0.001851       0.001851       0.001851        0.001851       0.001851              0             0
Statement           2           1      16.106771      16.106771      16.106771       16.106771      16.106771            276           276

А говорят, что для BLU индексов никаких не надо :)

Mark BarinsteinВремя ожидания суммируется по каждому из 8-ми параллельных агентов.
Понятно.

Спасибо.

...

Рейтинг:

0 / 0

01.04.2014, 17:40

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

index vs. fullscan

#38602227

Mark Barinstein

Гость

dsusr,

Что-то слишком сильно весело стало: это что, по строчной таблице меньше секунды стало выполняться?
А если на холодную (рестарт базы)?

...

Рейтинг:

0 / 0

01.04.2014, 18:07

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

index vs. fullscan

#38602434

dsusr

Гость

Mark Barinstein
Что-то слишком сильно весело стало: это что, по строчной таблице меньше секунды стало выполняться?
А если на холодную (рестарт базы)?
Хоть и 1-е апреля, но похоже на то (после пары десятков прогонов) :) MDC-вариант тоже 1.5 - 2 сек.
При этом время выполнения одинаковое как в 1 поток так и в 8

Правда теперь на этом фоне BLU-результат выглядит бледновато :)

Каждый запуск db2batch делаю на "холодном" буферпуле сразу после db2stop force;db2start;db2 connect to <dbname>

...

Рейтинг:

0 / 0

01.04.2014, 22:37

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

index vs. fullscan

#38602791

Mark Barinstein

Гость

dsusrПравда теперь на этом фоне BLU-результат выглядит бледновато :)Кластеризация (с помощью кластерного индекса или MDC) это, конечно, сильная штука.
Но там есть свои минусы:
- в случае с индексом она со временем ухудшается (надо реорганизовывать таблицу время от времени)
- в случае MDC надо заранее выбирать ключи
- в обоих случаях запрос с "неудобным" предикатом (не по полю клатерного индекса или по полю, не входящему в набор полей MDC) производительность будет заметно хуже

С BLU вы об этом не думаете.
А какой набор полей в synopsis таблице у вас?
Т.е. таблицы:

Код: sql

1.
2.
3.

select tabschema, tabname 
from syscat.tabdep 
where dtype='7' and btype='T' and bschema='DB2INST5' and bname='SUN_ROW'

...

Рейтинг:

0 / 0

02.04.2014, 11:37

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

index vs. fullscan

#38603106

dsusr

Гость

Mark Barinstein- в обоих случаях запрос с "неудобным" предикатом (не по полю клатерного индекса или по полю, не входящему в набор полей MDC) производительность будет заметно хуже
Да, это понятно. Просто интересовала "синтетика" на простом кейсе в сравнении c BLU (с прицелом на PoC)

В общем поигравшись с предикатом на предмет разных диапазонов PERIOD получил почти одинаковый рантайм на колоночной таблице. Стало понятно, что чего-то не так с data skiping'ом. База была профикспачена с 10.5.0 до 10.5.3 - может сломалось чего "по дороге"

Пересоздал и перегрузил колоночную таблицу, теперь исходный запрос "на холодную" по ней выполняется за :

Код: sql

1.
2.
3.
4.
5.
6.

* Summary Table:

Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
--------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- -------------
Statement           1           1       0.001095       0.001095       0.001095        0.001095       0.001095              0             0
Statement           2           1       0.445010       0.445010       0.445010        0.445010       0.445010            276           276

т.е где-то в 2 раза лучше, чем по кластерному индексу.
При этом в синопсис-таблице есть поля "PERIODMIN" и "PERIODMAX"

Теперь еще один вариант сравнения BLU c обычным (не кластерным и не-MDC) индексом :
Cоздан индекс по ACCNT_CODE, собрана статистика, по ACCNT_CODE='ZQTY' отбирается ~ 1.1 мил. записей :

Код: sql

1.
2.
3.
4.
5.

select "PERIOD", ANAL_T7, "D_C", COUNT(*), SUM("AMOUNT"), SUM("OTHER_AMT")
 FROM "DB2INST5"."SUN_ROW"
 WHERE  ACCNT_CODE='ZQTY'
  GROUP BY "PERIOD", ANAL_T7, "D_C"
  ORDER BY "PERIOD", ANAL_T7, "D_C"

По строчной таблице :

Код: sql

1.
2.
3.
4.
5.
6.

* Summary Table:

Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
--------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- -------------
Statement           1           1       0.002041       0.002041       0.002041        0.002041       0.002041              0             0
Statement           2           1      18.535283      18.535283      18.535283       18.535283      18.535283           2714          2714

По колоночной таблице :

Код: sql

1.
2.
3.
4.
5.
6.

* Summary Table:

Type      Number      Repetitions Total Time (s) Min Time (s)   Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
--------- ----------- ----------- -------------- -------------- -------------- --------------- -------------- -------------- -------------
Statement           1           1       0.001026       0.001026       0.001026        0.001026       0.001026              0             0
Statement           2           1      14.752673      14.752673      14.752673       14.752673      14.752673           2714          2714

И вот здесь уже в синопсис-таблице MIN/MAX распределений для колонки ACCNT_CODE нет.
Cобственно из 44-х колонок таблицы в синопсисе парами min/max описаны только 16

Есть где-то информация по какому принципу BLU выбирает поля для синопсис-таблицы ? И как-то повлиять на это можно ?

...

Рейтинг:

0 / 0

02.04.2014, 14:55

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

index vs. fullscan

#38603241

Mark Barinstein

Гость

dsusr...
И вот здесь уже в синопсис-таблице MIN/MAX распределений для колонки ACCNT_CODE нет.
Cобственно из 44-х колонок таблицы в синопсисе парами min/max описаны только 16

Есть где-то информация по какому принципу BLU выбирает поля для синопсис-таблицы ? И как-то повлиять на это можно ?По строковым полям эта информация хранится, если эти поля являются частью первичных или внешних ключей.
Не помню, появляются ли сами записи по полю, если на таблицу с данными навесить внешний ключ, содержащий это поле.

...

Рейтинг:

0 / 0

02.04.2014, 16:17

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

index vs. fullscan

#38605443

dsusr

Гость

Mark BarinsteinПо строковым полям эта информация хранится, если эти поля являются частью первичных или внешних ключей.
Не помню, появляются ли сами записи по полю, если на таблицу с данными навесить внешний ключ, содержащий это поле.
Ясно. Спасибо.
Маркетинг, рассказывая про data skiping, как-то об этом умалчивает :)

Получается, что :
1) дата-скиппинг работает только если колонки указанные в предикатах присутствуют в синопсисе, в остальных случаях BLU сканит всю колонку;
2) по-дефолту (без ключей) в синопсис попадают только численные колонки;
3) таблицы с большим количеством CHAR/VARCHAR/DATE/etc колонок могут быть не самым оптимальным вариантом для BLU;

...

Рейтинг:

0 / 0

04.04.2014, 13:35

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

index vs. fullscan

#38605508

Mark Barinstein

Гость

dsusrПолучается, что :
1) дата-скиппинг работает только если колонки указанные в предикатах присутствуют в синопсисе, в остальных случаях BLU сканит всю колонку;
2) по-дефолту (без ключей) в синопсис попадают только численные колонки;
3) таблицы с большим количеством CHAR/VARCHAR/DATE/etc колонок могут быть не самым оптимальным вариантом для BLU;
1) Да.
2) Нет. Еще DATE, TIME, TIMESTAMP.
3) Да, в данный момент повлиять на это можно только созданием внешних ключей (они всё равно not enforced) на какую-нибудь таблицу со строковым ПК.

...

Рейтинг:

0 / 0

04.04.2014, 14:25

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

index vs. fullscan

#38605692

dsusr

Гость

Mark Barinstein2) Нет. Еще DATE, TIME, TIMESTAMP.

Действительно. Это уже интереснее :)

Тогда еще один маленький тест. Таблица LINEITEM из TPC-H. 300M строк

Запрос :

Код: sql

1.
2.
3.
4.

select l_shipdate, l_shipmode, count(*), sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price
from lineitem
group by l_shipdate, l_shipmode
order by 1 desc

как полностью по всей таблице, так и с любым дипазоном дат в предикатах вида :

Код: sql

1.
2.
3.
4.
5.

where l_shipdate between '1994-12-01' and '1994-12-31'

where l_shipdate between '1994-06-01' and '1994-12-31'

where l_shipdate between '1994-01-01' and '1994-12-31'

выполняется одинаково за 25-26 сек. Инфа по колонке l_shipdate в синопсисе есть

В плане есть операции после CTQ (т.е. уже на строчном движке) :

Код: sql

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.

Access Plan:
-----------
        Total Cost:             744554
        Query Degree:           8

        Rows
       RETURN
       (   1)
        Cost
         I/O
         |
       214.452
       LMTQ
       (   2)
       744554
       112234
         |
       214.452
       TBSCAN
       (   3)
       744554
       112234
         |
       214.452
       SORT
       (   4)
       744554
       112234
         |
       214.452
       CTQ
       (   5)
       744554
       112234
         |
       214.452
       GRPBY
       (   6)
       744554
       112234
         |
     3.67052e+06
       TBSCAN
       (   7)
       744512
       112234
         |
     3.00006e+08
 CO-TABLE: DB2INST5
      LINEITEM
         Q1

Получается дата-скипинг не срабатывает ?

...

Рейтинг:

0 / 0

04.04.2014, 17:11

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

index vs. fullscan

#38605697

mustaccio

Участник

Откуда: Москва -> Торонто

Сообщения: 484

Рейтинг: 0 / 0

dsusrПолучается дата-скипинг не срабатывает ?

А вы посмотрите распределение дат по TSN в таблице синопсиса. Если таблица физически не отсортирована по дате, может получаться так, что приходится просматривать все куски в поисках нужных дат.

...

Рейтинг:

0 / 0

04.04.2014, 17:18

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

index vs. fullscan

#38605747

Mark Barinstein

Гость

dsusr,

Операции на строчном движке уже не добавляют стоимость запроса.

Как оно легло можно для диапазонов
where l_shipdate between '1994-12-01' and '1994-12-31'
where l_shipdate between '1994-06-01' and '1994-12-31'
where l_shipdate between '1994-01-01' and '1994-12-31'
как-то так посмотреть (в предположении, что нет пропусков по TSN):

Код: sql

1.
2.
3.
4.
5.
6.
7.

select 
  sum(tsnmax-tsnmin) + count(1) tsns_qualified
, (select max(a.tsnmax) - min(a.tsnmin) from sysibm.SYNxxx a) + 1 tsns_all
, count(1) ranges_qualified
, (select count(1) from sysibm.SYNxxx) ranges_all
from sysibm.SYNxxx
where l_shipdatemax >= '1994-12-01' and l_shipdatemin <= '1994-12-31'

...

Рейтинг:

0 / 0

04.04.2014, 18:10

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

index vs. fullscan

#38607145

dsusr

Гость

Mark Barinsteindsusr,

как-то так посмотреть (в предположении, что нет пропусков по TSN):

Код: sql

1.
2.
3.
4.
5.
6.
7.

select 
  sum(tsnmax-tsnmin) + count(1) tsns_qualified
, (select max(a.tsnmax) - min(a.tsnmin) from sysibm.SYNxxx a) + 1 tsns_all
, count(1) ranges_qualified
, (select count(1) from sysibm.SYNxxx) ranges_all
from sysibm.SYNxxx
where l_shipdatemax >= '1994-12-01' and l_shipdatemin <= '1994-12-31'

Интересно, спасибо.
Сделал еще одну копию колоночной LINEITEM, но уже c сортировкой при загрузке по l_shipdate

Данный запрос по синопcисам обеих таблиц возвращает :

Код: sql

1.
2.
3.
4.

BLU_TABLE         TSNS_QUALIFIED              TSNS_ALL  RANGES_QUALIFIED   RANGES_ALL  
----------  --------------------  --------------------  ----------------  -----------  
NON SORTED             300005811    432345564302565559            292976       292976  
SORTED                   3873792             300005811              3783       292975

И рантайм "на холодную" теперь :

Вся таблица - 18.7 сек.
12 месяцев - 3.5 сек.
1 месяц - 0.5 сек.

Мораль: предварительная сортировка не просто recomended, а must have :)

...

Рейтинг:

0 / 0

07.04.2014, 13:02

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

17 сообщений из 17, страница 1 из 1

Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / index vs. fullscan

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

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

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

start [/forum/topic.php?fid=43&msg=38605508&tid=1601119]:	0ms
get settings:	11ms
get forum list:	13ms
check forum access:	3ms
check topic access:	3ms
track hit:	159ms
get topic data:	8ms
get forum data:	2ms
get page messages:	51ms
get tp. blocked users:	1ms
others:	293ms

total:	544ms