（警告道歉和黑客行为…）
背景：
我有一个遗留应用程序，我希望避免重写它的许多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)
在我的测试数据集中，我有：
- 总计约550万行
- 大约370万行没有"关联"交易
- 大约180万行"相关"交易
- 大约5.5万个不同的实体（客户）
因此，大约三分之一的事务行是与某个早期事务"相关"的更新。生产数据在transactionid方面大约大25倍，在不同的entityid方面大约大8倍，并且1/3的比率用于事务更新。
该代码查询，特别是无效的VIEW，它被定义为：
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子查询，因此所有实体的事务都将被分组，从而导致55000个更大的子查询结果集和更长的查询时间。。。全部在写入时达到平均40行（在本例中为71行）。
如果不重写数百个代码库的SQL查询，我就无法进一步规范transactions表（比如说initial_transactions和updated_transactions表由related连接），其中许多查询以不同的方式使用自连接语义。
洞察：
我最初尝试使用WINDOW函数重写查询，但遇到了各种各样的问题（另一次是另一个SO问题），当我看到www_fdw将其WHERE子句作为GET/POST参数传递给HTTP时，我非常感兴趣的是，非常天真的查询可能会在没有太多重组的情况下得到优化。
Postgresql9.3手册说：
F.31.4.远程查询优化
postgres_fdw尝试优化远程查询以减少数量从外部服务器传输的数据。这是通过发送将WHERE子句查询到远程服务器以供执行，而不是检索当前查询不需要的表列。到降低查询执行错误的风险，不发送WHERE子句到远程服务器，除非它们仅使用内置数据类型，运算符和函数。子句中的运算符和函数必须也不可侵犯。
实际发送到远程服务器执行的查询可以使用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，但无论哪种方式都没有区别。
问题：
我能用一些技巧让这个公认的黑客行为奏效吗？
如果我正确理解了你的问题，答案是"否"。没有任何"技巧"可以让额外的where子句通过fdw包装器。
然而，我认为你可能优化了错误的东西。
我会替换整个collapsed_transactions视图。除非我遗漏了什么，否则它只取决于事务表。创建一个表，使用触发器保持更新，并且只向普通用户授予SELECT权限。如果你还没有从pgtap获得一些测试工具，那么你就可以开始了。
编辑：视图优化。
如果你只想针对视图优化一个查询，并且可以调整视图的定义，那么试试这个：
CREATE VIEW collapsed_transactions AS
SELECT
g.entityid, -- THIS HERE
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
entityid, -- THIS HERE
COALESCE(x.related, x.transactionid) AS initial,
max(x.transactionid) AS latest
FROM transactions x
GROUP BY entityid, COALESCE(x.related, x.transactionid)
) g
INNER JOIN transactions t ON t.transactionid = g.latest
INNER JOIN transactions i ON i.transactionid = g.initial;
请注意，子查询公开entityid并允许我们对其进行筛选。我假设entityid对于主项和相关项是常量，否则我看不出查询是如何工作的。这应该让规划者能够充分掌握问题，首先在entityid上使用索引，并将查询时间缩短到毫秒。