Logo
Logo

Эффективное разбиение данных PostgreSQL на страницы в пользовательском интерфейсе

Алексей Новиков

Jun 14, 2020

Как сделать разбиение данных PostgreSQL на страницы быстрее с помощью оценки количества строк набора записей.

Когда мы хотим отобразить данные из большой таблицы в пользовательском интерфейсе, мы обычно получаем их с помощью запроса

SELECT * FROM <table_name> WHERE <condition> ORDER BY <key> LIMIT <limit> OFFSET <offset>

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

Давайте посмотрим на стандартное представление данных.

Скриншот из https://abris.site/pagila/#list/customer

Здесь мы видим результат запроса, кнопки для переключения страниц и поле, указывающее общее количество строк.

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

SELECT count (*) FROM <table_name> WHERE <condition>

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

Создайте две таблицы. Первая (родительская) таблица будет содержать 10 тысяч строк (спасибо Joe Nelson за идею создать много случайных данных).

CREATE TABLE parent AS  
  SELECT  
    generate_series(0,1000) AS i,  
    substr(concat(md5(random()::text), md5(random()::text)), 1, (random() * 64)::integer + 1) AS s;  
ALTER TABLE parent ADD PRIMARY KEY (i);

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

CREATE TABLE child AS  
  SELECT  
    generate_series(0,10000000) AS i,  
    substr(concat(md5(random()::text), md5(random()::text)), 1, (random() * 64)::integer + 1) AS s;  
ALTER TABLE child ADD PRIMARY KEY (i);  
ALTER TABLE child ADD COLUMN parent_i integer REFERENCES parent (i);  
update child set parent_i = i % 1000;  
CREATE INDEX parent_index ON child(parent_i);

После создания таблиц нам нужно уведомить планировщика запросов о кардинально изменившемся размере таблицы.

VACUUM ANALYZE;

Для отображения табличных данных в интерфейсе необходимо выполнить запрос (мы не показываем фактически возвращенные строки, это пока не важно):

select * from child left join parent on child.parent_i = parent.i order by child.i limit 10;
…
Execution time: 0.278 ms

а также подсчитать количество строк в результате этого запроса:

select count (*) from child left join parent on child.parent_i = parent.i;count  
--------  
10000001
Execution time: 494.535 ms

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

Мы обнаружили эту проблему, когда использовали нашу платформу Абрис на большом объеме данных. Исправить это было необходимо для того, чтобы сделать пользовательский интерфейс более гибким и быстрым.

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

Оценка количества строк

Давайте попробуем оценить количество строк каким-то другим способом. Если вы посмотрите на запрос, то легко увидите, что количество строк в результате равно количеству строк в дочерней таблице. К счастью, PostgreSQL хранит примерную информацию о размере таблиц и ее легко получить.

SELECT reltuples :: bigint AS estimate FROM pg_class WHERE oid = ‘child’ :: regclass;estimate  
--------  
10009754
Execution time: 0.033 ms

Это эффективный способ, но он не совсем подходит для платформы Абрис, которая обладает поиском и фильтрацией, и отображает представления. Большинство запросов, отправляемых на сервер, содержат WHERE. Например, рассмотрим этот запрос:

select * from child left join parent on child.parent_i = parent.i where child.s like ‘b%’ order by child.i

First of all let’s get the number of rows in this query with the count function.

select count (*) from child left join parent on child.parent_i = parent.i where child.s like ‘b%’;count  
---------  
624294
Execution time: 377.466 ms

Точно, но долго. Посмотрим, что скажет планировщик.

explain select * from child left join parent on child.parent_i = parent.i where child.s like ‘b%’;QUERY PLAN  
------------  
Gather (cost = 1031.52..289768.97 **rows = 603811** width = 80)...  
...

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

Лучший подсчет оценки

Меньшее количество строк дает меньшую точность оценки. Для запросов, возвращающих небольшое количество строк, лучше использовать функцию count. Но это не всегда удается сделать. Рассмотрим два запроса, каждый из которых возвращает небольшое количество строк.

Первый:

select * from child left join parent on child.parent_i = parent.i where child.s like ‘abcde%’;

В нашей тестовой базе данных этот запрос возвращается с 13 строками. Функция подсчета по этому запросу занимает более 300 мс.

Второй запрос проще и использует гораздо меньше данных:

select * from parent where s like ‘a%’;

Он возвращает 60 строк, но функция count по этому запросу выполняется почти мгновенно — менее одной миллисекунды.

Как мы узнаем, когда мы должны использовать count, и когда мы должны быть удовлетворены приблизительным значением? Планировщик снова поможет нам. Давайте начнем с более простого запроса.

explain select * from parent where s like ‘a%’;
QUERY PLAN  
-----------------------------  
Seq Scan on parent (**cost = 0.00..21.51** rows = 61 width = 39)...

Планировщик вернул расчетное количество строк. К тому же было сказано, что этот запрос имеет очень низкую стоимость — всего 21,51 балла. Это дает нам основание понять, что количество строк в запросе может быть получено с помощью функции count.

explain select * from child left join parent on child.parent_i = parent.i where child.s like ‘abcde%’
QUERY PLAN  
-----------------------------  
Nested Loop Left Join  (**cost=1000.27..229135.67** rows=985 width=80)...

Здесь точность оценки количества рядов очень низкая. Однако стоимость запроса в 10 000 раз выше. Возможно, в этом случае лучше не перегружать сервер функцией подсчета.

Общий алгоритм

  1. Сначала вам нужно запустить сам запрос с LIMIT. Если запрос возвращает меньше строк, чем указано в LIMIT, то общее количество строк подсчитываться не будет, в противном случае перейдите к шагу 2
  2. Оцените количество строк и стоимость запроса с помощью команды EXPLAIN. Если COST запроса превышает указанный порог, то верните ROWS в качестве оценки количества строк, в противном случае перейдите к шагу 3.
  3. Определите количество строк с помощью функции count.

Этот алгоритм легко реализуется на языке программирования, если вы используете EXPLAIN в формате JSON. Мы реализовали его на нашем бесплатном сервере REST, исходный код которого доступен на GitHub.

Ни кода, ни особого дизайна. Просто установите и используйте.

© 2020 Research laboratory ABRIS Ltd