Как можно оптимизировать запрос с COUNT и SUM в PostgreSQL?

Question

[nurzhannogerbek]

Здравствуйте, товарищи! Помогите пожалуйста разобраться.

В своем проекте использую базу данных PostgreSQL:

PostgreSQL 11.0 (Debian 11.0-1.pgdg90+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

В базе данных есть таблица со следующей структурой:

| SURVEY_ID (uuid)                     | EMPLOYEE (varchar)     | STATUS (bool) | SEND (bool) | TREE_ORGANIZATION_ID (varchar) |
|--------------------------------------|------------------------|---------------|-------------|--------------------------------|
| bc90de33-62f9-4c6f-a7a6-6a76abb28b65 | [e-mail]    | true          | true        | \27623\27734\28304             |
| bc90de33-62f9-4c6f-a7a6-6a76abb28b65 | [e-mail]   | true          | true        | \27623\30556\30453             |
| bc90de33-62f9-4c6f-a7a6-6a76abb28b65 | [e-mail] | true          | true        | \27623\30558\30353\30354\28233 |

Для данной таблицы создал индексирование следующим образом:

CREATE INDEX INDEX_SURVEYS_EMPLOYEES_RELATIONSHIP ON SURVEYS_EMPLOYEES_RELATIONSHIP(SURVEY_ID, EMPLOYEE, STATUS, SEND, TREE_ORGANIZATION_ID);

Делаю подобного рода запрос. Стоит задача ускорить/оптимизировать данный запрос.

SELECT
	NULLIF(COUNT(*), 0) AS TOTAL_RESPONDENTS,
	SUM(CASE WHEN SURVEYS_EMPLOYEES_RELATIONSHIP.SEND = TRUE AND SURVEYS_EMPLOYEES_RELATIONSHIP.STATUS = TRUE THEN 1 ELSE 0 END) AS PAST_RESPONDENTS
FROM 
	SURVEYS_EMPLOYEES_RELATIONSHIP
WHERE
	SURVEYS_EMPLOYEES_RELATIONSHIP.SURVEY_ID = 'bc90de33-62f9-4c6f-a7a6-6a76abb28b65'
AND 
	SURVEYS_EMPLOYEES_RELATIONSHIP.TREE_ORGANIZATION_ID LIKE CONCAT('%', '\', '27623', '%')

Команда EXPLAIN для этого запроса выдает следующий план:


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

Answer 1

[Melkij]

PostgreSQL 11.0 (Debian 11.0-1.pgdg90+2)

Спасибо за поддержку в первые пару недель после релиза, но в чём смысл этим заниматься в 2019 году? Чуть ли не единственный ответ что можете ожидать на багрепорт: проверьте сначала на актуальной версии.

Для данной таблицы создал индексирование следующим образом:

Зачем?

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

count на MVCC базе. Для OLAP - ок, пусть себе шуршит.
Для чего-то более быстрого - прикручивать предварительно агрегированные срезы.

TREE_ORGANIZATION_ID (varchar)

см. ltree
Хотя судя по LIKE CONCAT('%', '\', '27623', '%') - скорее даже массив.

Answer 2

[Александр Аксентьев]

https://tatiyants.com/pev/#/plans/new