Медленный запрос Informix COUNT/GROUP BY, даже с соответствующим индексом

Итак, я сделал некоторые тесты.

TL;DR

• Измените тип столбца страны на CHAR(32), перестройте индекс, и вы должны иметь гораздо лучшую производительность.

Длинная версия:

Используется informix 12.10FC6DE на linux centos 7 (ВМ создана в виртуальном боксе). Размер страницы, используемой для пространства базы данных, составлял 2048 байт, буферный пул — 50000 страниц.

Создал таблицу (tst) с размером строки около 425 байт (в среднем 4 строки на страницу) с несколькими столбцами. Из этих столбцов один — country VARCHAR(32), а другой — static_country CHAR(32). Заполнил таблицу 499999 строками со столбцами country и static_country, равномерно распределенными по 25 названиям стран.

Создал 2 индекса, один для столбца country (idx1_tst), а другой для столбца static_country (idx2_tst).

В разделе таблицы было использовано 125000 страниц данных (с использованием oncheck -pT). В индексах было использовано около 1500 страниц (с использованием oncheck -pT).

A. Запустите запрос несколько раз, запустив ПОСЛЕДОВАТЕЛЬНОЕ СКАНИРОВАНИЕ (время выполнения составляло от 10 до 15 секунд):

SELECT --+ FULL (tst)
    country, COUNT(*)
FROM
    tst
GROUP BY
    country

DIRECTIVES FOLLOWED:
FULL ( tst )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 1415645
Estimated # of Rows Returned: 25
Temporary Files Required For: Group By

  1) mydb.tst: SEQUENTIAL SCAN


Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                tst

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     499999     499999    499999     00:12.17   140001

  type     rows_prod  est_rows  rows_cons  time       est_cost
  ------------------------------------------------------------
  group    25         25        499999     00:13.01   1275644

B. Запустите запрос несколько раз, запустив INDEX SCAN для индекса столбца country, который имеет тип VARCHAR(32) (время выполнения от 4 мин 30 с до 5 мин):

SELECT --+ INDEX (tst idx1_tst)
    country, COUNT(*)
FROM
    tst
GROUP BY
    country

DIRECTIVES FOLLOWED:
INDEX ( tst idx1_tst )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 3462411
Estimated # of Rows Returned: 25

  1) mydb.tst: INDEX PATH

    (1) Index Name: mydb.idx1_tst
        Index Keys: country   (Serial, fragments: ALL)


Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                tst

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     499999     499999    499999     04:49.71   3462411

  type     rows_prod  est_rows  rows_cons  time       est_cost
  ------------------------------------------------------------
  group    25         25        499999     04:50.51   1275644

C. Запустите запрос несколько раз, запустив INDEX SCAN для индекса столбца static_country, который имеет тип CHAR(32) (время выполнения от 2 до 3 секунд):

SELECT --+ INDEX (tst idx2_tst)
    static_country, COUNT(*)
FROM
    tst
GROUP BY
    static_country

DIRECTIVES FOLLOWED:
INDEX ( tst idx2_tst )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 16428
Estimated # of Rows Returned: 25

  1) mydb.tst: INDEX PATH

    (1) Index Name: mydb.idx2_tst
        Index Keys: static_country   (Key-Only)  (Serial, fragments: ALL)


Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                tst

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     499999     499999    499999     00:02.02   16429

  type     rows_prod  est_rows  rows_cons  time       est_cost
  ------------------------------------------------------------
  group    25         25        499999     00:02.72   1277132

Используя таблицу SMI sysptprof в базе данных sysmaster, я вижу следующие счетчики (используя onstat -z между запусками для сброса счетчиков):

1. In case A (SEQUENCIAL SCAN):
   • table tst partition:
     • lockreqs 499999
     • isreads 125001
     • bufreads 500060
     • pagreads 117532
2. In case B (INDEX SCAN on the VARCHAR type column):
   • table tst partition:
     • lockreqs 499999
     • isreads 499990
     • bufreads 999997
     • pagreads 348585
   • index idx1_tst partition:
     • lockreqs 499999
     • isreads 500009
     • bufreads 506961
     • pagreads 2545
3. In case C (INDEX SCAN on the CHAR type column):
   • index idx2_tst partition:
     • lockreqs 499999
     • isreads 500000
     • bufreads 502879
     • pagreads 1440

Итак, для ПОСЛЕДОВАТЕЛЬНОГО СКАНИРОВАНИЯ, как я и ожидал, есть только активность в разделе таблицы.

Для INDEX SCAN в столбце CHAR есть только активность в разделе индекса, как я и ожидал (объяснение содержит указание Key-Only).

Для INDEX SCAN в столбце VARCHAR есть активность как в таблице, так и в индексных разделах, чего я не ожидал (но, как указал Фернандо, объяснение не содержит индикации Key-Only).

Я не могу объяснить такое поведение от informix. Но коллега указал мне на эту запись в руководстве по производительности informix (версия 12.10FC6, глава 10, план запроса, план доступа):

Важно: Оптимизатор не выбирает сканирование только по ключу для столбца VARCHAR. Если вы хотите воспользоваться преимуществами сканирования только по ключу, используйте ALTER TABLE с предложением MODIFY, чтобы изменить столбец на тип данных CHAR.

Вещи, которые я бы попробовал:

• COUNT(1) вместо COUNT(*) на случай, если СУБД тупая
• тестирование запроса и проверка плана выполнения без индекса, потому что это может быть источником путаницы
• проверка того, какие запросы ускоряет индекс, и проба различных типов индексов