Алексей, оно пойдёт по индексу, если будет ожидать что он дешевле чем секскан.

Главное тут то, что не важно, джойн явный или же not exists -- выпрямление работает

test=# explain select from j1 where not exists (select from j2 where j2.i = j1.i) and j1.i < 100;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=0.85..851.60 rows=51 width=0)
   ->  Index Only Scan using j1_pkey on j1  (cost=0.42..10.21 rows=102 width=4)
         Index Cond: (i < 100)
   ->  Index Only Scan using j2_pkey on j2  (cost=0.42..8.24 rows=1 width=4)
         Index Cond: (i = j1.i)
(5 rows)

А тут и без j1.i < 100 по индексу

join_collapse_limit

неправда

test=# explain select from j1 where not exists (select from j2 where j2.i = j1.i);
                              QUERY PLAN
----------------------------------------------------------------------
 Hash Anti Join  (cost=15417.00..47797.50 rows=500000 width=0)
   Hash Cond: (j1.i = j2.i)
   ->  Seq Scan on j1  (cost=0.00..14425.00 rows=1000000 width=4)
   ->  Hash  (cost=7213.00..7213.00 rows=500000 width=4)
         ->  Seq Scan on j2  (cost=0.00..7213.00 rows=500000 width=4)
(5 rows)

Давай тогда в сортировку и лимит)

см. join_collapse_limit и from_collapse_limit

test=# explain select from j1 where not exists (select from j2 where j2.i = j1.i);
                              QUERY PLAN
----------------------------------------------------------------------
 Hash Anti Join  (cost=15417.00..47797.50 rows=500000 width=0)
   Hash Cond: (j1.i = j2.i)
   ->  Seq Scan on j1  (cost=0.00..14425.00 rows=1000000 width=4)
   ->  Hash  (cost=7213.00..7213.00 rows=500000 width=4)
         ->  Seq Scan on j2  (cost=0.00..7213.00 rows=500000 width=4)
(5 rows)

Тут действительно сек скан лучше, смысла в индексе нет. Но сортировка и лимит реальная ситуация, и там другое дело)

Суть тут не в этом, а в том, что, как показал @samokhvalov, планы получаются одинаковые для той (EXISTS) и другой (LEFT JOIN ... IS NULL) вариантов записи такого запроса.

я не понимаю, что хочешь до меня донести

вот две пары примеров,
речь о том, что для планера предложенный вариант (тобой же) с JOIN -- то же самое что и NOT EXISTS, выполняется одинаково
какой там метод доступа будет, зависит от статистики. Сделаем лимит (с сортировкой или без) -- скорее будет индекс, не сделаем -- скорее секскан. Но важно что везде anti join нода в дереве, Постгрес различает именно эту операцию

другими словами, классно что можно с JOIN и топикстартеру должно подойти, если подзапросы никак

а если запрос будет посерьезней? Будет ли такая же красивая картинка если добавится например еще пяток джойнов?

см тут, как раз оно определяет, до каких пор трансформация присходит

если очень много джойнов -- там уже сумрак и geqo 🙂

нет, я как раз имел в виду, что все в пределах, но не просто 1 left join, а если их будет еще 4 например. Будет ли это также эффективно?

Вот,  меня сейчас Николай в ступор ввел) я от реального опыта отталкиваюсь, на постгре 11.5.4 и 12.1.1, так был вот такой реальный пример по миллионам записей, посеръезней чем то, что написано выше, так exists там куда лучше был чем вариант с left join, в том случае не было варианта без seq scan, с not exists он по btree мог гулять

посмотрите доку про join_collapse_limit и from_collapse_limit плз

Посмотрю, спасибо

Filter: (gvkey = ANY ('{~1600 items}'::integer[]))
                           Rows Removed by Filter: 1536564

Может все же будет у вас время глянуть.
Партиции пока подкинуть не удалось. Уменьшил кол-во вхождений в запросе IN, меньше уже сатанисты не дают.
В итоге получаю около 6.5 минут на запрос. Но с таким хаком: создал временную таблицу:

create temp table csco_idesind_2008_2020 as
select gvkey, datadate, datafmt from csco_idesind where datadate BETWEEN '2008-01-01 00:00:00' AND '2020-07-01 00:00:00' order by datadate;

Запрос в итоге таков:

ex

plain analyze SELECT k.gvkey, k.datadate, i.valuei, i.item, i.effdate, i.thrudate, k.datafmt
    FROM csco_ifndq AS i
    JOIN csco_idesind_2008_2020 AS k ON k.coifnd_id = i.coifnd_id
    WHERE k.gvkey IN (...500)
               AND
               k.datadate BETWEEN '2013-10-01 00:00:00' AND '2015-09-01 00:00:00'

и выхлоп анализатора:

Merge Join  (cost=62421496.84..4363959802.57 rows=286713043089 width=80) (actual time=359989.974..388725.779 rows=16478093 loops=1)
  Merge Cond: (k.coifnd_id = i.coifnd_id)
  ->  Sort  (cost=372058.39..372484.13 rows=170296 width=58) (actual time=16753.887..16763.030 rows=68442 loops=1)
        Sort Key: k.coifnd_id
        Sort Method: quicksort  Memory: 8420kB
        ->  Bitmap Heap Scan on csco_idesind_2008_2020 k  (cost=4748.49..357261.64 rows=170296 width=58) (actual time=995.278..16737.223 rows=68442 loops=1)
              Recheck Cond: ((datadate >= '2013-10-01 00:00:00'::timestamp without time zone) AND (datadate <= '2015-09-01 00:00:00'::timestamp without time zone))
              Filter: (gvkey = ANY ('{...500}'::integer[]))
              Rows Removed by Filter: 5502370
              Heap Blocks: exact=35703
              ->  Bitmap Index Scan on csco_idesind_2008_2020_datadate_gvkey_idx  (cost=0.00..4705.91 rows=184935 width=0) (actual time=769.804..769.805 rows=5570812 loops=1)
                    Index Cond: ((datadate >= '2013-10-01 00:00:00'::timestamp without time zone) AND (datadate <= '2015-09-01 00:00:00'::timestamp without time zone))
  ->  Materialize  (cost=62049438.45..63733054.29 rows=336723168 width=30) (actual time=232312.818..345640.447 rows=301097183 loops=1)
        ->  Sort  (cost=62049438.45..62891246.37 rows=336723168 width=30) (actual time=232312.813..315589.341 rows=285480123 loops=1)
              Sort Key: i.coifnd_id
              Sort Method: external merge  Disk: 14937712kB
              ->  Seq Scan on csco_ifndq i  (cost=0.00..6301306.68 rows=336723168 width=30) (actual time=0.852..114092.114 rows=336723160 loops=1)
Planning Time: 0.364 ms
JIT:
  Functions: 15
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.436 ms, Inlining 8.045 ms, Optimization 130.210 ms, Emission 64.295 ms, Total 203.985 ms
Execution Time: 389780.055 ms

надо конкретное смотреть

я всего лишь хотел рассказать про anti join. Который где-то года с 2008 как сейчас помню (как раз когда я XML пилил, эпоха уже прошла)

так -- надо конкретику смотреть

Ага, я только сейчас если честно познакомился с данной нодой)