1) psql (PostgreSQL) 11.9 (Debian 11.9-0+deb10u1)
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
2) Запрос
SELECT "t1"."index_id" FROM "cache_table" AS "t1" INNER JOIN "houses" AS "t2" ON ("t1"."id_house" = "t2"."id") WHERE
(("t2"."id_addresses""t1"."index_id" DESC LIMIT 100 OFFSET 0
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
daniilak=# \d houses
                                    Table "public.houses"
    Column     |       Type       | Collation | Nullable |              Default
---------------+------------------+-----------+----------+------------------------------------
 id            | integer          |           | not null | nextval('houses_id_seq'::regclass)
 id_addresses  | integer          |           | not null |
 object_type   | integer          |           | not null |
 building_type | integer          |           | not null |
 level         | integer          |           | not null |
 levels        | integer          |           | not null |
 rooms         | integer          |           | not null |
 area          | double precision |           | not null |
 kitchen_area  | double precision |           | not null |
 cache         | text             |           |          |
Indexes:
    "houses_pkey" PRIMARY KEY, btree (id)
    "houses_index" btree (id_addresses)
    "houses_index_bt" btree (building_type)
    "houses_index_level" btree (level)
    "houses_index_levels" btree (levels)
    "houses_index_ot" btree (object_type)
    "houses_index_rooms" btree (rooms)
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
daniilak=# \d cache_table
                              Table "public.cache_table"
  Column   |   Type   | Collation | Nullable |                 Default
-----------+----------+-----------+----------+-----------------------------------------
 id        | integer  |           | not null | nextval('cache_table_id_seq'::regclass)
 id_house  | integer  |           |          |
 index_id  | integer  |           |          |
 year      | smallint |           |          |
 month     | smallint |           |          |
 day       | smallint |           |          |
 is_actual | smallint |           |          |
Indexes:
    "cache_table_pkey" PRIMARY KEY, btree (id)
    "cache_id_house" btree (id_house)
    "cache_index_id" btree (index_id)
    "cache_table_house_id_index_id" btree (id_house, index_id)
    "cache_year" btree (year)

daniilak=#

___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
4) EXPLAIN (ANALYZE, BUFFERS).
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1001.16..14871.56 rows=100 width=4) (actual time=1053.867..2323.809 rows=100 loops=1)
   Buffers: shared hit=1799709 read=297868
   ->  Gather Merge  (cost=1001.16..520607739.31 rows=3753366 width=4) (actual time=1053.865..2323.786 rows=100 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=1799709 read=297868
         ->  Nested Loop  (cost=1.14..520173507.72 rows=1563902 width=4) (actual time=1019.601..2001.823 rows=46 loops=3)
               Buffers: shared hit=1799709 read=297868
               ->  Parallel Index Scan Backward using cache_index_id on cache_table t1  (cost=0.57..118451325.04 rows=62600747 width=8) (actual time=0.035..139.189 rows=124142 loops=3)
                     Buffers: shared hit=124767 read=108346
               ->  Index Scan using houses_pkey on houses t2  (cost=0.56..6.42 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=372426)
                     Index Cond: (id = t1.id_house)
                     Filter: (id_addresses = ANY ('{1300337,17473466,721185,17619259,619419,2732564,1192743,1338491,2220830,2499588,2295809,1400032,2999239,1826678,2937551,238916,1627147,1098697,722126,11702520,975851,11702689,11675572,11733586,752619,1721555,1048003,17567056,334995,...,17572575}'::integer[]))
                     Rows Removed by Filter: 1
                     Buffers: shared hit=1674942 read=189522
 Planning Time: 4.559 ms
 Execution Time: 2323.861 ms
(17 rows)
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
___________________________________________________________________________________________________
5) создал индекс
CREATE INDEX cache_table_house_id_index_id ON public.cache_table USING btree (id_house, index_id);
Сделал vacuum full verbose houses;
Сделал vacuum full verbose cache_table;
6)