PostgreSQL CROSS JOIN索引性能优化问题（第二部分）

首先得明确：纯无过滤的CROSS JOIN（生成两个表的全笛卡尔积）基本没法靠索引优化——因为数据库必须遍历两个表的所有行来生成组合，这种场景下的优化方向通常是减少参与JOIN的行数（比如先对两个表做过滤再JOIN）、提升硬件资源（内存、IO），或者检查业务逻辑是否真的需要笛卡尔积（毕竟大多数业务场景下，带条件的JOIN才是合理的）。

如果是带过滤/聚合条件的CROSS JOIN，那索引就能发挥作用了，结合你的表结构，给你几个具体的优化方向：

1. 针对过滤条件创建复合索引 #

如果你的CROSS JOIN语句后跟着WHERE条件筛选main_transaction的字段，比如：

SELECT * 
FROM main_transaction mt
CROSS JOIN some_other_table sot
WHERE mt.user_id = 1001 AND mt.profile_id = 5002;

这种情况下，给过滤字段创建复合索引是最高效的，比如：

CREATE INDEX idx_mt_user_profile ON public.main_transaction(user_id, profile_id);

这样数据库会先通过索引快速定位到符合条件的main_transaction行，再和另一张表做笛卡尔积，直接减少了参与JOIN的行数，性能提升会很明显。

如果过滤条件只用到单个字段（比如仅user_id），可以单独建单字段索引：

CREATE INDEX idx_mt_user_id ON public.main_transaction(user_id);

不过要注意：PostgreSQL不会自动给外键创建索引，你的user_id是外键但没有自带索引，所以这个索引不仅能优化CROSS JOIN，对其他涉及user_id的查询也有帮助。

2. 优化关联/聚合场景的索引 #

如果CROSS JOIN之后还有关联其他表或者聚合操作，比如：

SELECT mt.request_no, COUNT(*)
FROM main_transaction mt
CROSS JOIN some_other_table sot
WHERE mt.profile_id = 5002
GROUP BY mt.request_no;

那针对聚合字段request_no创建索引，能加速分组计算：

CREATE INDEX idx_mt_request_no ON public.main_transaction(request_no);

如果是后续关联其他表，比如关联main_profile，那确保关联字段（比如profile_id）有索引即可。

3. 利用执行计划排查问题 #

不管建什么索引，都要用EXPLAIN ANALYZE查看执行计划，确认索引是否被正确使用：

EXPLAIN ANALYZE
SELECT * FROM main_transaction mt CROSS JOIN some_other_table sot WHERE mt.user_id = 1001;

如果执行计划里显示Seq Scan（全表扫描），那可能是统计信息过时，先执行ANALYZE public.main_transaction;更新统计信息，再看是否能触发索引扫描。

4. 尽量替换不必要的CROSS JOIN #

很多时候开发者用CROSS JOIN加WHERE过滤，其实可以换成INNER JOIN带ON条件，比如：

-- 不推荐：CROSS JOIN加WHERE
SELECT * 
FROM main_transaction mt
CROSS JOIN jhi_user u
WHERE mt.user_id = u.id;

-- 推荐：INNER JOIN带ON条件
SELECT * 
FROM main_transaction mt
INNER JOIN jhi_user u ON mt.user_id = u.id;

后者的语义更清晰，数据库也能更好地优化执行计划，性能通常比CROSS JOIN加WHERE更好。

内容的提问来源于stack exchange，提问作者Sanatbek Matlatipov