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

@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, но только усеченной по столбцам таблицы), т.е. единственный плюс индекса только в том что он меньше по объему и его быстрее читать чем большую таблицу, но по своему прямому предназначению он не работает.

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Реализация движения на Box2D v3 - трение и коллизии с повёрнутыми стенами 8Observer8 20.02.2026 Содержание блога Box2D позволяет легко создать главного героя, который не проходит сквозь стены и перемещается с заданным трением о препятствия, которые можно располагать под углом, как верхнее. . .	Конвертировать закладки radiotray-ng в m3u-плейлист damix 19.02.2026 Это можно сделать скриптом для PowerShell. Использование . \СonvertRadiotrayToM3U. ps1 <path_to_bookmarks. json> Рядом с файлом bookmarks. json появится файл bookmarks. m3u с результатом. # Check if. . .	Семь CDC на одном интерфейсе: 5 U[S]ARTов, 1 CAN и 1 SSI Eddy_Em 18.02.2026 Постепенно допиливаю свою "многоинтерфейсную плату". Выглядит вот так: https:/ / www. cyberforum. ru/ blog_attachment. php?attachmentid=11617&stc=1&d=1771445347 Основана на STM32F303RBT6. На борту пять. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .
И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»	SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .

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

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

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

@Swa111 912 / 286 / 57 Регистрация: 01.06.2023 Сообщений: 810
	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 912 / 286 / 57 Регистрация: 01.06.2023 Сообщений: 810
	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

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

Решение