Индекс для поиска записей, в которых внешний ключ не существует.

Лучшее, что я могу придумать, это ваша последняя идея в комментариях: < strong>материализованное представление.

CREATE MATERIALIZED VIEW orphaned_products AS
SELECT *
FROM   products p
WHERE  NOT EXISTS (SELECT 1 FROM transactions t WHERE t.product_id = p.id)

Затем вы можете использовать эту таблицу (материализованное представление — это просто таблица) в качестве замены большой таблицы products в запросах, работающих с осиротевшими продуктами — с очевидным большим влиянием на производительность (несколько 100 строк вместо 100 миллионов). Для материализованных представлений требуется Postgres 9.3, но это то, что вы используете в соответствии с комментариями. И вы можете легко реализовать это вручную в более ранних версиях.

Однако материализованное представление представляет собой снимок и не обновляется динамически. (В любом случае это может аннулировать любое преимущество в производительности.) Для обновления вы запускаете (дорогостоящую) операцию:

REFRESH MATERIALIZED VIEW orphaned_products;

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

Конечно, у вас будет индекс для orphaned_products.id, но это не будет очень важно для маленькой таблицы из нескольких сотен строк.

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

CREATE TABLE orphaned_products2 AS
SELECT *
FROM   products p
WHERE  NOT EXISTS (SELECT 1 FROM transactions t WHERE t.product_id = p.id);

Конечно, вы можете обновить это материализованное представление, как и первое, усекая и заполняя его заново. Но смысл в том, чтобы избежать дорогостоящей операции. Все, что вам на самом деле нужно, это:

• Добавить новые продукты в orphaned_products2.
  Реализовать с помощью триггер AFTER INSERT ON products.

• Удалите продукты из orphaned_products2, как только в таблице transactions появится соответствующая строка.
  Реализуйте с помощью триггера AFTER UPDATE OF product_id ON transations. Только если ваша модель позволяет обновлять transations.products_id, что было бы необычно.
  И еще один AFTER INSERT ON transations.

Все сравнительно дешевые операции.

• Если транзакции также могут быть удалены, вам понадобится еще один триггер для добавления потерянных продуктов AFTER DELETE ON transations, что будет немного дороже. Для каждой удаленной транзакции вам нужно проверить, была ли она последней ссылкой на связанный продукт, и в этом случае добавить потерянную транзакцию. Все еще может быть намного дешевле, чем обновлять все материализованное представление.

VACUUM

После вашей дополнительной информации я бы также предложил пользовательские настройки для агрессивной очистки orphaned_products2, так как это создаст много мертвых строк.

Я попытался попробовать некоторые тестовые данные и нашел способ, который, как мне кажется, более быстрый, USING THE EXCEPT OPERATOR

Ниже приведены мои выводы.

Тестовые данные

CREATE TABLE TestTable_1 
(ID INT IDENTITY PRIMARY KEY,
Column1 INT,
Column2 INT
)
GO

CREATE TABLE TestTable_2
(ID INT IDENTITY PRIMARY KEY,
Column1 INT,
Column2 INT,
FK_ID INT references TestTable_1(ID)
)
GO

DECLARE @i INT = 1

WHILE (@i <= 10000)
 BEGIN
   INSERT INTO TestTable_1 (Column1, Column2)
   VALUES (@i , @i + 100)
   SET @i = @i + 1;
 END

 DECLARE @i2 INT = 1

WHILE (@i2 <= 10000)
 BEGIN
   INSERT INTO TestTable_2 (Column1, Column2, FK_ID)
   VALUES (@i2 , @i2 + 100, 1 + CONVERT(INT, (10000-1+1)*RAND()))
   SET @i2 = @i2 + 1;
 END

 UPDATE  TestTable_2
 SET FK_ID = NULL
 WHERE ID IN (SELECT TOP 10 ID FROM TestTable_2 ORDER BY NEWID())

Отфильтрованный индекс во второй таблице

CREATE NONCLUSTERED INDEX FIX_FK_ID
ON TestTable_2(ID, FK_ID)
WHERE FK_ID IS NULL ;
GO

Запрос 1

SET STATISTICS IO ON;
PRINT 'TEST 1'
SELECT T1.*
FROM TestTable_1 T1 LEFT JOIN TestTable_2 T2
ON T1.ID = T2.FK_ID
WHERE FK_ID IS NOT NULL

Запрос 2

PRINT 'TEST 2'
SELECT ID,  Column1,    Column2 FROM TestTable_1
EXCEPT 
SELECT ID,  Column1,    Column2 FROM TestTable_2
WHERE FK_ID IS NULL

TEST 1

(9990 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestTable_1'. Scan count 1, logical reads 28, physical reads 0, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestTable_2'. Scan count 1, logical reads 33, physical reads 3, read-ahead reads 29, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


TEST 2

(9990 row(s) affected)
Table 'TestTable_1'. Scan count 1, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestTable_2'. Scan count 1, logical reads 22, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Планы выполнения для обоих запросов