Используйте postgres_fdw для ускорения просмотра, содержащего несколько самостоятельных объединений в группе.

(предупреждение об извинениях и хакерских атаках...)

Предыстория:

У меня есть устаревшее приложение, для которого я хотел бы избежать переписывания большого количества кода SQL. Я пытаюсь ускорить определенные типы очень дорогостоящих запросов, которые он выполняет (например, низко висящие плоды).

У него есть книга финансовых транзакций, представленная таблицей transactions. Когда вставляется новая строка, триггерная функция (здесь не показана) переносит новый баланс для данного объекта.

Некоторые типы внешних эффектов модели транзакций (например, платежи в пути) путем пометки новых транзакций «связанной» транзакцией, чтобы приложение могло группировать связанные транзакции вместе.

\d transactions

                  Table "public.transactions"
       Column        |   Type    | Modifiers 
---------------------+-----------+-----------
 entityid            | bigint    | not null
 transactionid       | bigint    | not null default nextval('tid_seq')
 type                | smallint  | not null
 status              | smallint  | not null
 related             | bigint    | 
 amount              | bigint    | not null
 abs_amount          | bigint    | not null
 is_credit           | boolean   | not null
 inserted            | timestamp | not null default now()
 description         | text      | not null
 balance             | bigint    | not null

Indexes:
    "transactions_pkey" PRIMARY KEY, btree (transactionid)
    "transactions by entityid" btree (entityid)
    "transactions by initial trans" btree ((COALESCE(related, transactionid)))

Foreign-key constraints:
    "invalid related transaction!" FOREIGN KEY (related) 
                                   REFERENCES transactions(transactionid)

В моем наборе тестовых данных у меня есть:

• всего около 5,5 миллионов строк
• примерно 3,7 миллиона строк без «связанной» транзакции
• примерно 1,8 миллиона строк со «связанной» транзакцией
• примерно 55 тыс. различных идентификаторов объектов (клиентов).

Таким образом, около 1/3 всех строк транзакций являются обновлениями, «связанными» с какой-либо более ранней транзакцией. Производственные данные примерно в 25 раз больше по transactionid и примерно в 8 раз больше по отдельным entityid, а соотношение 1/3 сохраняется для обновлений транзакций.

Код запрашивает особенно неэффективное представление, которое было определено как:

CREATE VIEW collapsed_transactions AS
SELECT t.entityid,
    g.initial,
    g.latest,
    i.inserted AS created,
    t.inserted AS updated,
    t.type,
    t.status,
    t.amount,
    t.abs_amount,
    t.is_credit,
    t.balance,
    t.description
FROM ( SELECT 
          COALESCE(x.related, x.transactionid) AS initial,
          max(x.transactionid) AS latest
       FROM transactions x
       GROUP BY COALESCE(x.related, x.transactionid)
     ) g
INNER JOIN transactions t ON t.transactionid = g.latest
INNER JOIN transactions i ON i.transactionid = g.initial;

и типичные запросы имеют вид:

SELECT * FROM collapsed_transactions WHERE entityid = 204425;

Как вы можете видеть, предложение where entityid = 204425 не будет использоваться для ограничения подзапроса GROUP BY, поэтому все транзакции entitids будут сгруппированы, что приведет к увеличению набора результатов подзапроса на 55 000 и увеличению времени выполнения запроса... все, чтобы получить в среднем 40 строк (71 в этом примере) на момент написания.

Я не могу еще больше нормализовать таблицу transactions (например, объединив таблицы initial_transactions и updated_transactions с помощью related) без переписывания сотен SQL-запросов кодовой базы, многие из которых по-разному используют семантику самосоединения.

Информация:

Сначала я пытался переписать запросы, используя WINDOW-функции, но столкнулся с этим всевозможными проблемами (еще один вопрос SO в другой раз), когда увидел, что www_fdw передает свое предложение WHERE в HTTP в качестве параметров GET/POST, меня очень заинтриговала возможность оптимизации очень наивных запросов без особой реструктуризации.

В руководстве по Postgresql 9.3 говорится:

F.31.4. Удаленная оптимизация запросов

postgres_fdw пытается оптимизировать удаленные запросы, чтобы уменьшить объем данных, передаваемых с внешних серверов. Это делается путем отправки предложений запроса WHERE на удаленный сервер для выполнения и отказа от получения столбцов таблицы, которые не нужны для текущего запроса. Чтобы снизить риск неправильного выполнения запросов, предложения WHERE не отправляются на удаленный сервер, если они не используют только встроенные типы данных, операторы и функции. Операторы и функции в предложениях также должны быть IMMUTABLE.

Запрос, который фактически отправляется на удаленный сервер для выполнения, можно просмотреть с помощью EXPLAIN VERBOSE.

Попытка:

Поэтому я подумал, что, возможно, я мог бы поместить GROUP-BY в представление, рассматривать это представление как внешнюю таблицу и что оптимизатор передаст предложение WHERE в эту внешнюю таблицу, что приведет к гораздо более эффективному запросу....

CREATE VIEW foreign_transactions_grouped_by_initial_transaction AS 
  SELECT
    entityid,
    COALESCE(t.related, t.transactionid) AS initial,
    MAX(t.transactionid) AS latest
  FROM transactions t
  GROUP BY
    t.entityid,
    COALESCE(t.related, t.transactionid);

CREATE FOREIGN TABLE transactions_grouped_by_initial_transaction 
  (entityid bigint, initial bigint, latest bigint) 
  SERVER local_pg_server 
  OPTIONS (table_name 'foreign_transactions_grouped_by_initial_transaction');

EXPLAIN ANALYSE VERBOSE
  SELECT 
    t.entityid, 
    g.initial, 
    g.latest, 
    i.inserted AS created, 
    t.inserted AS updated, 
    t.type, 
    t.status,
    t.amount,
    t.abs_amount,
    t.is_credit,
    t.balance,
    t.description
  FROM transactions_grouped_by_initial_transaction g 
  INNER JOIN transactions t on t.transactionid = g.latest
  INNER JOIN transactions i on i.transactionid = g.initial 
  WHERE g.entityid = 204425;

и это работает очень хорошо!

 Nested Loop  (cost=100.87..305.05 rows=10 width=116) 
              (actual time=4.113..16.646 rows=71 loops=1)
   Output: t.entityid, g.initial, g.latest, i.inserted, 
           t.inserted, t.type, t.status, t.amount, t.abs_amount, 
           t.balance, t.description
   ->  Nested Loop  (cost=100.43..220.42 rows=10 width=108) 
                    (actual time=4.017..10.725 rows=71 loops=1)
         Output: g.initial, g.latest, t.entityid, t.inserted, 
                 t.type, t.status, t.amount, t.abs_amount, t.is_credit,
                 t.balance, t.description
     ->  Foreign Scan on public.transactions_grouped_by_initial_transaction g
                 (cost=100.00..135.80 rows=10 width=16) 
                 (actual time=3.914..4.694 rows=71 loops=1)
            Output: g.entityid, g.initial, g.latest
            Remote SQL: 
              SELECT initial, latest
              FROM public.foreign_transactions_grouped_by_initial_transaction
              WHERE ((entityid = 204425))
         ->  Index Scan using transactions_pkey on public.transactions t  
                  (cost=0.43..8.45 rows=1 width=100) 
                  (actual time=0.023..0.035 rows=1 loops=71)
               Output: t.entityid, t.transactionid, t.type, t.status, 
                       t.related, t.amount, t.abs_amount, t.is_credit, 
                       t.inserted, t.description, t.balance
               Index Cond: (t.transactionid = g.latest)
   ->  Index Scan using transactions_pkey on public.transactions i  
            (cost=0.43..8.45 rows=1 width=16) 
            (actual time=0.021..0.033 rows=1 loops=71)
         Output: i.entityid, i.transactionid, i.type, i.status, 
                 i.related, i.amount, i.abs_amount, i.is_credit, 
                 i.inserted, i.description, i.balance
         Index Cond: (i.transactionid = g.initial)
 Total runtime: 20.363 ms

Проблема:

Однако, когда я пытаюсь запечь это в VIEW (с другим уровнем postgres_fdw или без него), оптимизатор запросов, похоже, не проходит через предложение WHERE :-(

CREATE view collapsed_transactions_fast AS
  SELECT 
    t.entityid, 
    g.initial, 
    g.latest, 
    i.inserted AS created, 
    t.inserted AS updated, 
    t.type, 
    t.status,
    t.amount,
    t.abs_amount,
    t.is_credit,
    t.balance,
    t.description
  FROM transactions_grouped_by_initial_transaction g 
  INNER JOIN transactions t on t.transactionid = g.latest
  INNER JOIN transactions i on i.transactionid = g.initial;

EXPLAIN ANALYSE VERBOSE
  SELECT * FROM collapsed_transactions_fast WHERE entityid = 204425; 

приводит к:

Nested Loop  (cost=534.97..621.88 rows=1 width=117) 
             (actual time=104720.383..139307.940 rows=71 loops=1)
  Output: t.entityid, g.initial, g.latest, i.inserted, t.inserted, t.type, 
          t.status, t.amount, t.abs_amount, t.is_credit, t.balance, 
          t.description
  ->  Hash Join  (cost=534.53..613.66 rows=1 width=109) 
                 (actual time=104720.308..139305.522 rows=71 loops=1)
        Output: g.initial, g.latest, t.entityid, t.inserted, t.type, 
                t.status, t.amount, t.abs_amount, t.is_credit, t.balance, 
                t.description
        Hash Cond: (g.latest = t.transactionid)
    ->  Foreign Scan on public.transactions_grouped_by_initial_transaction g
         (cost=100.00..171.44 rows=2048 width=16) 
         (actual time=23288.569..108916.051 rows=3705600 loops=1)
           Output: g.entityid, g.initial, g.latest
           Remote SQL: 
            SELECT initial, latest 
            FROM public.foreign_transactions_grouped_by_initial_transaction
        ->  Hash  (cost=432.76..432.76 rows=142 width=101) 
                  (actual time=2.103..2.103 rows=106 loops=1)
              Output: 
                t.entityid, t.inserted, t.type, t.status, t.amount, 
                t.abs_amount, t.is_credit, t.balance, t.description, 
                t.transactionid
              Buckets: 1024  Batches: 1  Memory Usage: 14kB
              ->  Index Scan using "transactions by entityid" 
                  on public.transactions t  
                     (cost=0.43..432.76 rows=142 width=101) 
                     (actual time=0.049..1.241 rows=106 loops=1)
                    Output: t.entityid, t.inserted, t.type, t.status, 
                            t.amount, t.abs_amount, t.is_credit, 
                            t.balance, t.description, t.transactionid
                    Index Cond: (t.entityid = 204425)
  ->  Index Scan using transactions_pkey on public.transactions i  
        (cost=0.43..8.20 rows=1 width=16) 
        (actual time=0.013..0.018 rows=1 loops=71)
        Output: i.entityid, i.transactionid, i.type, i.status, i.related, 
                i.amount, i.abs_amount, i.is_credit, i.inserted, i.description, 
                 i.balance
        Index Cond: (i.transactionid = g.initial)
Total runtime: 139575.140 ms

Если я смогу внедрить это поведение в VIEW или FDW, я смогу просто заменить имя VIEW в очень небольшом количестве запросов, чтобы сделать его намного более эффективным. Меня не волнует, будет ли это очень медленным для какого-то другого варианта использования (более сложное предложение WHERE), и я назову VIEW, чтобы отразить его предполагаемое использование.

use_remote_estimate имеет значение по умолчанию FALSE, но в любом случае это не имеет значения.

Вопрос:

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