Оптимализация (ускорение) запроса с max() и group by

akorud · Сообщение **akorud** » 21 янв 2008, 17:06

Имеется таблица

reads  (
    id         bigint,
    input_id   integer,
    reg_date   TIMESTAMP NOT NULL)

в ней около 10 млн. записей.
И запрос

Код: Выделить всё

SELECT max(r.reg_date), input_id FROM reads r GROUP BY input_id

- задача найти дату последнего (самого нового) read для каждого input. Время исполнения около 10 минут. Автоматически используется только индекс по input_id, индекс по reg_date не подцепляется ни автоматически ни через хинт:

Код: Выделить всё

Plan
------------------------------------------------
PLAN (R ORDER FK_READS_1)

Adapted Plan
------------------------------------------------
PLAN (R ORDER FK_READS_1)

Query Time
------------------------------------------------
Prepare       : 31,00 ms
Execute       : 109 141,00 ms
Avg fetch time: 5 197,19 ms

Memory
------------------------------------------------
Current: 23 435 388
Max    : 143 154 736
Buffers: 25 000

Operations
------------------------------------------------
Read   : 1 009 574
Writes : 157 824
Fetches: 32 005 774


Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
|        Table Name        |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts |
|                          |   Total   |   reads   |    reads    |         |         |         |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
|                     READS|         0 |  10127946 |           0 |       0 |       0 |       0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+

При отключении всех индексов - plan (r natural) - сокращается до около 30с:

Код: Выделить всё

Query
------------------------------------------------
SELECT max(r.reg_date) as maks, input_id as d FROM reads r GROUP BY input_id plan (r natural)


Plan
------------------------------------------------
PLAN SORT ((R NATURAL))

Adapted Plan
------------------------------------------------
PLAN SORT ((R NATURAL))

Query Time
------------------------------------------------
Prepare       : 31,00 ms
Execute       : 22 562,00 ms
Avg fetch time: 1 074,38 ms

Memory
------------------------------------------------
Current: 10 454 576
Max    : 10 490 560
Buffers: 25 000

Operations
------------------------------------------------
Read   : 63 797
Writes : 0
Fetches: 14 155 323


Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
|        Table Name        |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts |
|                          |   Total   |   reads   |    reads    |         |         |         |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
|                     READS|         0 |         0 |     7013881 |       0 |       0 |       0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+

Вопрос - можно ли как-то еще ускорить запрос? Сервер FB 2.0.3/Linux, dual xeon, 2G RAM. Изменять config FB можно как угодно если надо, пока только кеш увеличен.

Сообщение **kdv** » 21 янв 2008, 17:54

http://forum.ibase.ru/phpBB2/viewtopic.php?t=4175
здесь про это (примерно) написано. И быстрее 10 сек. не будет.

Вопрос - можно ли как-то еще ускорить запрос?

практически - нет. теоретически - можно, изменением структуры данных. Т.е хранить max_id по конкретной дате, в отдельной таблице.

akorud · Сообщение **akorud** » 21 янв 2008, 18:01

kdv писал(а):http://forum.ibase.ru/phpBB2/viewtopic.php?t=4175
здесь про это (примерно) написано. И быстрее 10 сек. не будет.

Вопрос - можно ли как-то еще ускорить запрос?
практически - нет. теоретически - можно, изменением структуры данных. Т.е хранить max_id по конкретной дате, в отдельной таблице.

Так я и думал. Спасибо.

dimitr · Сообщение **dimitr** » 21 янв 2008, 21:04

добавлю лишь, что тюнинг параметра SortMemUpperLimit позволяет ускорить обработку внешней (aka "дисковой") сортировки за счет большего буфера в памяти.

Кузнецов Евгений

Доброго времени суток!

To kdv & dimitr

Кстати, а если бы в FB была бы реализована возможность оптимизатора Yaffil использовать составной индекс и для отбора, и для сортировки, то запрос

Код: Выделить всё

select i.id,(select first 1 r.reg_date from reads r where r.input_id = i.id order by r.reg_date)
from inputs i

при наличии справочной таблицы inputs и индекса

Код: Выделить всё

create desc index idx_reads on reads(input_id,reg_date)

улучшил бы ситуацию?
Вроде бы должно быть только одно индексное чтение для подзапроса.
В FB 2.0.3, разумеется, он дает худшие результаты, чем вариант с SORT-планом.
P.S.
Разумеется, мне следовало бы скачать Yaffil, установить, выполнить тест, но что-то уже энергии на сегодня не осталось

Кузнецов Евгений

Прошу прощения, ошибся в запросе:

Код: Выделить всё

select i.id,(select first 1 r.reg_date from reads r where r.input_id = i.id order by r.reg_date desc)
from inputs i

akorud · Сообщение **akorud** » 21 янв 2008, 23:35

dimitr писал(а):добавлю лишь, что тюнинг параметра SortMemUpperLimit позволяет ускорить обработку внешней (aka "дисковой") сортировки за счет большего буфера в памяти.

А кстати откуда тут SORT? И-за max? Мне вобщем-то не нужно сортировать результат.

Сообщение **kdv** » 22 янв 2008, 00:38

group by делается сортировкой. Вот, блин, когда людям советуешь прочитать материал - никогда ж не читают. См. ссылку в моем посте.

dimitr · Сообщение **dimitr** » 22 янв 2008, 08:59

Кузнецов Евгений писал(а):Кстати, а если бы в FB была бы реализована возможность оптимизатора Yaffil использовать составной индекс и для отбора, и для сортировки, то запрос
Код: Выделить всё
select i.id,(select first 1 r.reg_date from reads r where r.input_id = i.id order by r.reg_date)
from inputs i
при наличии справочной таблицы inputs и индекса
Код: Выделить всё
create desc index idx_reads on reads(input_id,reg_date)
улучшил бы ситуацию?

да, конечно