将两个表合并到第三个表中

I want to union to tables and join them with a third metadata table and I would like to know which approach is the best/fastest?
The database is a PostgreSQL.
Below is my two suggestions, but other approaches are welcome.

我想将它们合并到表中并与第三个元数据表连接，我想知道哪种方法是最好的/最快的?数据库是一个PostgreSQL。下面是我的两个建议，但是欢迎其他方法。

To do the join before the union on both tables:

在两张桌子上，在工会面前做加入:

SELECT a.id, a.feature_type, b.datetime, b.file_path
FROM table1 a, metadata b WHERE a.metadata_id = b.id
UNION ALL
SELECT a.id, a.feature_type, b.datetime, b.file_path
FROM table2 a, metadata b WHERE a.metadata_id = b.id

Or to do the union first and then do the join:

或者先做联合然后再做联合:

SELECT a.id, a.feature_type, b.datetime, b.file_path
FROM
(
    SELECT id, feature_type, metadata_id FROM table1
    UNION ALL
    SELECT id, feature_type, metadata_id FROM table2
)a, metadata b
WHERE a.metadata_id = b.id

3 个解决方案

#1

Run an EXPLAIN ANALYZE on both statements then you will see which one is more efficient.

在两个语句上运行EXPLAIN分析，然后您将看到哪个更有效。

#2

it can be unpredictable due to sql-engine optimizator. it's better to look at the execution plan. finally both approaches can be represented in the same way

由于sql引擎优化器，它可能是不可预测的。最好看看执行计划。最后两种方法都可以用相同的方式表示。

#3

In so far as I can remember, running Explain will reveal that PostgreSQL interprets the second as the first provided that there is no group by clause (explicit, or implicit due to union instead of union all) in any of the subqueries.

就我所知，运行Explain将揭示PostgreSQL将第二个解释为第一个，前提是在任何子查询中都没有group by子句(显式的，或隐式的，因为union而不是union all)。

#1