Странное поведение запроса

@bsd9 · Регистрация: 01.12.2014

Студворк — интернет-сервис помощи студентам

Доброго времени суток!
PostgeSQL 11.7 Ent, 24 ядра cpu, 400 гигабайт ОЗУ.

Имею запрос:

SQL
1
SELECT COUNT(DISTINCT(d_code)) FROM v_data WHERE s_time > (now() at TIME zone 'UTC' - INTERVAL '120 seconds');

Получаю такой план запроса:

SQL
1
2
3
4
5
6
7
8
Aggregate  (cost=72893208.22..72893208.23 ROWS=1 width=8) (actual TIME=256895.282..256895.283 ROWS=1 loops=1)
   Buffers: shared hit=6058190 READ=12748549 dirtied=353 written=24672, temp READ=5147 written=5149
   ->  INDEX ONLY Scan USING v_data_idx ON v_data  (cost=0.71..72891495.16 ROWS=685224 width=14) (actual TIME=4.542..251978.318 ROWS=2232042 loops=1)
         INDEX Cond: (server_time > (timezone('UTC'::text, now()) - '00:02:00'::INTERVAL))
         Heap Fetches: 3221413
         Buffers: shared hit=6058190 READ=12748549 dirtied=353 written=24672
 Planning TIME: 0.171 ms
 Execution TIME: 256895.401 ms

Таблица работает в aappend-only режиме, т.е. каждую минуту прибывают данные. Вижу в плане запроса shared hit=6058190 read=1274854 - не понимаю почему для нахождения этого условия необходимо перелопатить 97 гигабайт данных с диска? И как в данной ситуации я могу на это повлиять?

Схема таблицы:

Кликните здесь для просмотра всего текста

Code
1
2
3
4
5
6
7
8
9
10
-----------------+--------------------------------+--------------------+-------------------+--------------------------------------------
 id              | bigint                         |                    | not null          | nextval('v_data_seq'::regclass)
 d_code          | character varying(32)          |                    |                   |
 t_time          | timestamp(0) without time zone |                    | not null          |
 s_time          | timestamp(0) without time zone |                    | not null          |
 sens_           | numeric(18,0)                  |                    |                   |
 sens2_          | numeric(18,0)                  |                    |                   |
 a_num           | integer                        |                    |                   |
 a_data          | double precision               |                    |                   |
 flags           | bigint                         |                    |                   |

"id_pk" PRIMARY KEY, btree (id)
"v_data_idx" btree (d_code, t_time, s_time)
"ad_null" btree (s_time) WHERE a_data IS NULL
"nav_data_dev_time" btree (d_code, t_time)

Заранее спасибо!

Добавлено через 1 час 21 минуту
Забыл указать настройки сервера:

Кликните здесь для просмотра всего текста

max_connections = 500
shared_buffers = 100GB
effective_cache_size = 300GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 52428kB
huge_pages = try
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 24
max_parallel_workers_per_gather = 4
max_parallel_workers = 24
max_parallel_maintenance_workers = 4

@Swa111 · 22.08.2024, 09:26

мб из за частых обновлений таблица сильно фрагментирована, попробуйте выполнить вакуум и анализ таблиц. А данные вставляются одной транзакцией или построчно разными транзакциями?

@bsd9 · 22.08.2024, 09:45 **[ТС]**

Сообщение от Swa111

мб из за частых обновлений таблица сильно фрагментирована, попробуйте выполнить вакуум и анализ таблиц. А данные вставляются одной транзакцией или построчно разными транзакциями?

Построчно разными транзакциями.
Так же забыл указать, что размер таблицы в районе 1ТБ.

Сообщение от Swa111

попробуйте выполнить вакуум и анализ таблиц

Это же надо либо VACUUM FULL либо pg_repack?

@Swa111 · 22.08.2024, 11:05

Сообщение от bsd9

Построчно разными транзакциями.
Так же забыл указать, что размер таблицы в районе 1ТБ.

Это объясняет такую сильную фрагментацию.

Возможно поможет перу упорядочивание строк если допустим раз в час делать в одной транзакции удаление всех вставленных записей и вставка одним блоком. Затем обычный вакуум что бы удалить освободившиеся страницы.

Ну а вообще запрос не очень оптимален изначально. каково вообще число уникальных значений d_code? и какие индексы еще есть на таблице? Интервал в 2 часа фиксированный или могут быть другие варианты?

@bsd9 · 22.08.2024, 16:23 **[ТС]**

Swa111,
объясняет фрагментацию вставкой по одной строке?

Сообщение от Swa111

Возможно поможет перу упорядочивание строк если допустим раз в час делать в одной транзакции удаление всех вставленных записей и вставка одним блоком. Затем обычный вакуум что бы удалить освободившиеся страницы.

Такое мне пока недоступно, не могу на приложение влиять. Могу только бороться с последствиями.

Сообщение от Swa111

каково вообще число уникальных значений d_code? и какие индексы еще есть на таблице? Интервал в 2 часа фиксированный или могут быть другие варианты?

Цель запроса - посмотреть уникальное количество d_code за последние две минуты. Там не два часа, а две минуты 120 sec. интервал фиксированный. Сколько уникальных их значений всего не считал если честно, посчитаю напишу.
Индексы еще такие присутствуют:

"id_pk" PRIMARY KEY, btree (id)
"v_data_idx" btree (d_code, t_time, s_time)
"ad_null" btree (s_time) WHERE a_data IS NULL
"nav_data_dev_time" btree (d_code, t_time)

@Swa111 · 23.08.2024, 08:07

Много данных приходится прочитать так как используемый индекс не подходящий. Есть возможность создать еще один?

@bsd9 · 27.08.2024, 00:17 **[ТС]**

Сообщение от Swa111

Много данных приходится прочитать так как используемый индекс не подходящий. Есть возможность создать еще один?

Есть, только вот какой?

@Swa111 · 27.08.2024, 07:04

Индекс для решения в лоб

SQL
1
(s_time,d_code)

уже должен значительно уменьшить время для поиска. Если время все равно будет слишком большим есть у меня еще одно решение, но оно довольно объемное с точки зрения запроса.

При этом так как индекс "nav_data_dev_time" btree (d_code, t_time) является частностью "v_data_idx" btree (d_code, t_time, s_time), то nav_data_dev_time можно удалить (хотя это может не много сказаться на производительности)

@bsd9 · 28.08.2024, 09:51 **[ТС]**

Swa111,
Здравствуйте, помогло. Спасибо.

Только совсем не понял, почему помогло? Ведь d_code нет в условии этого запроса.

@Swa111 · 28.08.2024, 10:10

d_code есть в колонках. Если индекс содержит все колонки которые необходимы для выполнения запроса (условие и выдача), то оптимизатор даже не читает основную таблицу ограничиваясь только индексом. В исходном случае это не сработало так как s_time в индексе "v_data_idx" btree (d_code, t_time, s_time) находится на последнем месте и по нему нельзя искать (блокируют неизвестные d_code, t_time). При этом ПГ понял что дешевле прочитать весь индекс (а он скорее всего как раз и весит 90 гб) чем таблицу.

В случае с индексом (s_time,d_code), так как колонка (s_time) по которой происходит фильтрация находится в префиксном положении, то при выполнении запроса даже не нужно читать весь индекс, а только ту часть что соответствует условию, бонусом рядом лежит колонка нужная для выборки, поэтому не нужно ходить в основную таблицу.

@bsd9 · 30.08.2024, 16:15 **[ТС]**

Сообщение от Swa111

Если индекс содержит все колонки которые необходимы для выполнения запроса (условие и выдача), то оптимизатор даже не читает основную таблицу ограничиваясь только индексом.

Этот узел называется Only Index Scan. Что с индексом который вы порекомендовали, что без него планировщик выбирает Only Index Scan, но только без индекса ему сначала придется найти все столбцы d_code подходящие под условие, потом t_time, потом s_time. В новым же индексом, сразу s_time и d_code.
Но и том и том случае будет Only Index Scan.

Правильно ли я рассуждаю?

@Swa111 · 30.08.2024, 22:17

Давайте попробую объяснить на пример таблиц Excel (очень условно). Допустим у вас есть большая таблица. данные в нее вставляются просто в конец. для того что бы понять что такое индекс скопируйте столбцы s_time d_code и номера строк, и поместите их на отдельный лист затем отсортируйте последовательно по s_time и d_code. Этот лист как раз и будет индексом. Так как данные отсортированы то можно быстрым бинарным поиском найти место где начинаются данные s_time > now - 2 минуты (на пример экселя быстрой прокруткой найти нужную строку). При этом данные сгрппированы и можно считывать последовательно. Так как в индексе так же хранится d_code, то сразу его используем. если бы нам нужны были данные которых нет в индексе, то по номеру строки из индекса мы идем в основную таблицу, забираем данные из указанной строки и извлекаем нужное поле.
А теперь возьмем пример с индексом d_code, t_time, s_time данные отсортированы в том же порядке как указаны столбцы, но теперь строки с нужнми s_time > now - 2 минуты могут располагаться где угодно, поэтому приходится просмотривать весь индекс строку за строкой (по факту full scan, но только усеченной по столбцам таблицы), т.е. единственный плюс индекса только в том что он меньше по объему и его быстрее читать чем большую таблицу, но по своему прямому предназначению он не работает.

Новые блоги и статьи Все статьи Все блоги /
Валидация и контроль данных табличной части документа перед записью Maks 22.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа, разработанного в КА2. Задача: контроль и валидация данных табличной части документа перед записью с учетом регламента компании. . .	Отчёт о затраченных материалах за определенный период с макетом печатной формы Maks 21.04.2026 Отчёт из решения ниже размещён в конфигурации КА2. Задача: разработка отчёта по затраченным материалам за определённый период, с возможностью вывода печатной формы отчёта с шапкой и подвалом. В. . .	Отчёт о спецтехнике находящейся в ремонте Maks 20.04.2026 Отчёт из решения ниже размещен в конфигурации КА2. Задача: отобразить спецтехнику, которая на данный момент находится в ремонте. Есть нетиповой документ "Заявка на ремонт спецтехники" который. . .	Памятка для бота и "визитка" для читателей "Semantic Universe Layer (Слой семантической вселенной)" Hrethgir 19.04.2026 Сгенерировано для краткого описания по случаю сборки и компиляции скелета серверного приложения. И пусть после этого скажут, что статьи сгенерированные AI - туфта и не интересно. И это не реклама -. . .
Запрет удаления строк ТЧ документа при определённом условии Maks 19.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "Аккумуляторы", разработанного в конфигурации КА2. У данного документа есть ТЧ, в которой в зависимости от прав доступа. . .	Модель заражения группы наркоманов alhaos 17.04.2026 Условия задачи сформулированы тут Суть: - Группа наркоманов из 10 человек. - Только один инфицирован ВИЧ. - Колются одной иглой. - Колются раз в день. - Колются последовательно через. . .	Мысли в слух. Про "навсегда". kumehtar 16.04.2026 Подумалось тут, что наверное очень глупо использовать во всяких своих установках понятие "навсегда". Это очень сильное понятие, и я только начинаю понимать край его смысла, не смотря на то что давно. . .	My Business CRM MaGz GoLd 16.04.2026 Всем привет, недавно возникла потребность создать CRM, для личных нужд. Собственно программа предоставляет из себя базу данных клиентов, в которой можно фиксировать звонки, стадии сделки, а также. . .

@Swa111 918 / 291 / 58 Регистрация: 01.06.2023 Сообщений: 816
	22.08.2024, 09:26
	мб из за частых обновлений таблица сильно фрагментирована, попробуйте выполнить вакуум и анализ таблиц. А данные вставляются одной транзакцией или построчно разными транзакциями? 0

@Swa111 918 / 291 / 58 Регистрация: 01.06.2023 Сообщений: 816
	23.08.2024, 08:07
	Много данных приходится прочитать так как используемый индекс не подходящий. Есть возможность создать еще один? 0

@bsd9 0 / 0 / 0 Регистрация: 01.12.2014 Сообщений: 118
	28.08.2024, 09:51 [ТС]
	Swa111, Здравствуйте, помогло. Спасибо. Только совсем не понял, почему помогло? Ведь d_code нет в условии этого запроса. 0

@Swa111 918 / 291 / 58 Регистрация: 01.06.2023 Сообщений: 816
	28.08.2024, 10:10
	d_code есть в колонках. Если индекс содержит все колонки которые необходимы для выполнения запроса (условие и выдача), то оптимизатор даже не читает основную таблицу ограничиваясь только индексом. В исходном случае это не сработало так как s_time в индексе "v_data_idx" btree (d_code, t_time, s_time) находится на последнем месте и по нему нельзя искать (блокируют неизвестные d_code, t_time). При этом ПГ понял что дешевле прочитать весь индекс (а он скорее всего как раз и весит 90 гб) чем таблицу. В случае с индексом (s_time,d_code), так как колонка (s_time) по которой происходит фильтрация находится в префиксном положении, то при выполнении запроса даже не нужно читать весь индекс, а только ту часть что соответствует условию, бонусом рядом лежит колонка нужная для выборки, поэтому не нужно ходить в основную таблицу. 1

@Swa111 918 / 291 / 58 Регистрация: 01.06.2023 Сообщений: 816
	30.08.2024, 22:17
	Давайте попробую объяснить на пример таблиц Excel (очень условно). Допустим у вас есть большая таблица. данные в нее вставляются просто в конец. для того что бы понять что такое индекс скопируйте столбцы s_time d_code и номера строк, и поместите их на отдельный лист затем отсортируйте последовательно по s_time и d_code. Этот лист как раз и будет индексом. Так как данные отсортированы то можно быстрым бинарным поиском найти место где начинаются данные s_time > now - 2 минуты (на пример экселя быстрой прокруткой найти нужную строку). При этом данные сгрппированы и можно считывать последовательно. Так как в индексе так же хранится d_code, то сразу его используем. если бы нам нужны были данные которых нет в индексе, то по номеру строки из индекса мы идем в основную таблицу, забираем данные из указанной строки и извлекаем нужное поле. А теперь возьмем пример с индексом d_code, t_time, s_time данные отсортированы в том же порядке как указаны столбцы, но теперь строки с нужнми s_time > now - 2 минуты могут располагаться где угодно, поэтому приходится просмотривать весь индекс строку за строкой (по факту full scan, но только усеченной по столбцам таблицы), т.е. единственный плюс индекса только в том что он меньше по объему и его быстрее читать чем большую таблицу, но по своему прямому предназначению он не работает. 1

Странное поведение запроса

Решение