Как ускорить поиск по индексу, где много null?

Question

[chemdev]

Имеется таблица 6 млн строк, в ней foreign key к таблице 30 строк. Долго ищет обычные запросы (минуты), при этом count(*) тех же запросов - моментально. В чем может быть проблема?
При этом важно отметить, что, например, запросы по текстовому индексу к этой таблице выполняются быстро (максимум секунды, в выборках могут быть десятки тысяч строк и все быстро извлекается).

Comments

[Everything_is_bad]

так надо начать с explain analyze для проблемного запроса, далее, что такое "обычные запросы"? ну и кроме этого, есть частичные индексы, из которого можно исключать по условию, те же null

[chemdev]

Everything_is_bad, блин частичные индексы топ, не знал, спасибо!

[chemdev]

Everything_is_bad, протестирую на запросах закину сюда сравнение для моего случая

[chemdev]

Everything_is_bad, explain analyze ошибается на 7 порядков.

Bitmap Heap Scan on "my_table"  (cost=382.01..98244.14 rows=33236 width=335) (actual time=16.535..125347.800 rows=12327 loops=1)
   Recheck Cond: (last_modified_user_id = 1)
   Heap Blocks: exact=10206
   ->  Bitmap Index Scan on distinct_last_modified_user_id_ce6ebb4d  (cost=0.00..373.70 rows=33236 width=0) (actual time=10.260..10.260 rows=122079 loops=1)
         Index Cond: (last_modified_user_id = 1)
 Planning Time: 0.096 ms
 Execution Time: 125350.313 ms
(7 rows)

[Everything_is_bad]

chemdev, это на частичном? ну и такая разница намекаем на проблемы со статистиком, VACUUM ANALYZE на таблицу, если позволяет доступность

[chemdev]

Everything_is_bad, нет, это с обычным индексом для внешнего ключа. Частичный строится, vacuum analyze делал.

CREATE INDEX "last_modified_user_notnull_idx" ON "my_table" ("last_modified_user_id") WHERE "last_modified_user_id" IS NOT NULL;

[Everything_is_bad]

chemdev, вынеси это в вопрос и добавить сам запрос, ну и структуру таблиц в нем участвующих, я ща уже не готов подробно это анализировать, а так больше вариантов, что другие увидят

[Melkij]

explain analyze ошибается на 7 порядков

поясните?
я вижу только 3-4 кратную ошибку. На 7 порядков, т.е. в 7млн раз, ошибку не вижу.

и покажите explain (analyze,buffers) при включенном track_io_timing

[chemdev]

Melkij, ну planning примерно в 10^7 меньше executing time, я об этом. Частичный индекс решил проблему, скоро выложу решение с эксплейнами и структурой таблиц.

[Melkij]

Planning Time - это время затраченное на работу планировщика, вся машинерия по генерации и выбора плана выполнения запроса, а не предполагаемое время работы запроса. Execution Time - выполнение запроса по выбранному плану. Время выполнения запроса это Planning Time + Execution Time + передача результата клиенту по сети (+ ещё пара неожиданных сюрпризов)

[chemdev]

Melkij, спасибо, неправильно думал до этого.

Answer 1

[chemdev]

Для ускорения работы запроса был создан частичный индекс по проблемному столбцу, выглядит так:

CREATE INDEX "last_modified_user_notnull_idx" ON "my_table" ("last_modified_user_id") WHERE "last_modified_user_id" IS NOT NULL;

После этого запросы по типу

SELECT * FROM "my_table" where last_mofied_user_id=x;

перестали зависать.
Также в таких ситуациях стоит выполнить VACUUM ANALYZE "my_table";

Структура таблиц

CREATE TABLE "user" (
    id INT GENERATED ALWAYS AS IDENTITY,
    PRIMARY KEY(id)
)

CREATE TABLE "my_table" (
    id bigserial INT GENERATED ALWAYS AS IDENTITY
    last_modfied_user_id int default NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_last_modified_user FOREIGN KEY(last_modfied_user_id) REFERENCES user(id)
)

За помощь в решении выражаю благодарность Everything_is_bad