Привет Есть табличка на 400млн строк, вот с таким индексом btree

("BalanceId" asc, "Id" desc);
и два запроса делающих одно и тоже но время различается в разы

SELECT *
FROM public."BalanceOperation"
where "BalanceId" in (select "Id" from "Balance" b where "UserAccountId" = 102982)
order by "Id" desc limit 20

Limit (cost=1.00..1335089.74 rows=20 width=104) (actual time=24862.239..120159.578 rows=20 loops=1)
-> Nested Loop (cost=1.00..301196020.26 rows=4512 width=104) (actual time=24862.237..120159.560 rows=20 loops=1)
Join Filter: ("BalanceOperation"."BalanceId" = b."Id")
Rows Removed by Join Filter: 1236580880
-> Index Scan Backward using "PK_BalanceOperation" on "BalanceOperation" (cost=0.57..16868837.74 rows=403300800 width=104) (actual time=0.019..5201.942 rows=30914530 loops=1)
-> Materialize (cost=0.43..183.50 rows=47 width=8) (actual time=0.000..0.001 rows=40 loops=30914530)
-> Index Scan using "IX_Balance_UserAccountId_CurrencyId" on "Balance" b (cost=0.43..183.27 rows=47 width=8) (actual time=0.014..0.074 rows=40 loops=1)
Index Cond: ("UserAccountId" = 102982)
Planning time: 0.270 ms
Execution time: 120159.618 ms

SELECT *
FROM public."BalanceOperation"
where "BalanceId" in (2710863,2710850,2710875,2710873,2710872,2710871,2710867,2710870,2710866,
2710860,2710865,2710855,2710864,2710868,2710861,2710859,2710858,2710857,2710856,2710854,2710853,
2710852,2710851,2710862,2710874,4706940,2710869,2748275,2851227,2955089,3058072,3161055,3264038,
3367021,4499834,4603646,4814553,4917716,5065002,5180464)
order by "Id" desc limit 20

Limit (cost=0.57..249.79 rows=20 width=104) (actual time=1789.782..8607.939 rows=20 loops=1)
-> Index Scan Backward using "PK_BalanceOperation" on "BalanceOperation" (cost=0.57..37033332.69 rows=2971953 width=104) (actual time=1789.781..8607.930 rows=20 loops=1)
Filter: ("BalanceId" = ANY ('{2710863,2710850,2710875,2710873,2710872,2710871,2710867,2710870,2710866,2710860,2710865,2710855,2710864,2710868,2710861,2710859,2710858,2710857,2710856,2710854,2710853,2710852,2710851,2710862,2710874,4706940,2710869,2
Rows Removed by Filter: 30914510
Planning time: 0.161 ms
Execution time: 8607.957 ms

вопрос - почему планировщик не оптимизировал запрос с подзапросом?

а если написать SELECT DISTINCT в подзапросе?

тоже самое

ваш индекс никак не используется (ни в 1-м, ни во 2-м примере). попробуйте создать индекс без DESC, просто ("BalanceId", "Id")

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

хотя нет, теперь читает с правильной стороны. но это почему то не помогло

покажите определение таблиц так, как его выводит \d+ в psql

Table "public.BalanceOperation" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------+-----------------------------+-----------+----------+------------------------------------------------+----------+--------------+------------- Id | bigint | | not null | nextval('"BalanceOperation_Id_seq"'::regclass) | plain | | BalanceId | bigint | | not null | | plain | | OperationId | bigint | | not null | | plain | | OperationType | integer | | not null | | plain | | ... DateCreated | timestamp without time zone | | not null | | plain | | CreatedBy | text | | | | extended | | Indexes: "PK_BalanceOperation" PRIMARY KEY, btree ("Id") "IX_BalanceOperation_BalanceId_Id" UNIQUE, btree ("BalanceId", "Id") Foreign-key constraints: "FK_BalanceOperation_Balance_BalanceId" FOREIGN KEY ("BalanceId") REFERENCES "Balance"("Id") ON DELETE CASCADE

у вас в запросе 2 таблицы же используются…