优化大型表上的SQL连接

Question

我正在PostgreSQL数据库上运行一个大型表（每天700万新行）的sql查询集合，并且已经遇到了第一个视图和现在创建表的一些性能问题。大多数我使用的命令类似于下面的查询：

CREATE TABLE events_tb AS 

SELECT * 

FROM 
(SELECT column1, column2, column3, column4, column5 
FROM test_database_1 
WHERE column6 = 'value1' 
    AND date_column > '2012-07-01' 
    AND date_column < '2012-07-10' 
) a 

INNER JOIN (SELECT DISTINCT column1 FROM test_database_2) b 
     ON (a.column1 = b.column1); 

是否有提高报表上述集合占的事实，查询正在对非常大的表运行的方法吗？

Answer 1

这应该是简单，更快捷：

CREATE TABLE events_tb AS 
SELECT column1, column2, column3, column4, column5 
FROM test_database_1 t1 
WHERE column6 = 'value1' 
AND date_column > '2012-07-01' 
AND date_column < '2012-07-10' 
AND EXISTS (
    SELECT 1 
    FROM test_database_2 t2 
    WHERE t2.column1 = t1.column1 
    ); 

你有它会的方式在新创建的表中包含column1两次，这会导致错误消息。

一个EXISTS semi-join应该比JOIN或IN表达更快，因为它可以在第一次发现停止执行。这对复制品尤其有益 - 从您的查询中的DISTINCT来看，您似乎拥有这些复制品。

Answer 2

我什么都不知道PostgreSQL的优化器，但你可以尝试用IN (SELECT...)结构替换INNER JOIN：

CREATE TABLE events_tb AS 

SELECT column1, column2, column3, column4, column5 

FROM test_database_1 

WHERE column6 = 'value1' 
    AND date_column > '2012-07-01' 
    AND date_column < '2012-07-10' 
    AND column1 IN (SELECT DISTINCT column1 FROM test_database_2)