英文:
PostgreSQL join and where clause execution order
问题
我有两个表格在PostgreSQL上绑定了外键，如下所示：
tableA (id, status)
- 索引;
- (id)
- (status)
tableB (id, tableAId, result)
- 索引;
- (id, tableAId)
- (tableAId)
`tableB`通过使用`tableAId`字段与`tableA`绑定了外键。
我想要根据特定条件连接这两个表格（所有筛选都基于索引）。有两种应用筛选条件的方式，但我想了解哪种方式更有效。以下是这两种方式：
- select * from tableA a join tableB b on a.id = b.tableAId where a.status = 'X' and b.id = 123
- select * from tableA a join tableB b on a.id = b.tableAId and a.status = 'X' and b.id = 123
正如您所看到的，第一个查询包含在`where`部分的筛选条件，而第二个查询在`join`的`on`部分包含它们。据我所知，`join`操作在`where`子句之前执行，所以我预期第二个查询将比第一个查询具有更好的性能。我正确吗？
英文:
I have two tables that are bound with foreign key on PostgreSQL like below;
tableA (id, status)
- indexes;
- (id)
- (status)
tableB (id, tableAId, result)
- indexes;
- (id, tableAId)
- (tableAId)
tableB is bound to tableA with foreign key by using tableAId field.
I want to join both tables by specific conditions (all filters based on indexes). There are two ways to apply filters, but I'd like to learn which way is more performant. Here is the ways;
- select * from tableA a join tableB b on a.id = b.tableAId where a.status = 'X' and b.id = 123
- select * from tableA a join tableB b on a.id = b.tableAId and a.status = 'X' and b.id = 123
As you can see, the first query contains filters in where section, but the second one contains them in on section of the join. As far as I know, join operations are done before where clause, so I expect the second query will have better performance compared to the first one. Am I correct?
答案1
得分: 1
你可以在特定查询之前加上EXPLAIN，以查看执行计划，这将解释PostgreSQL将如何尝试执行查询。甚至可以在查询前加上EXPLAIN ANALYZE来检查查询执行时的性能。
然而，重要的是要理解查询的执行计划是根据查询应尽快正确执行的理念设置的。因此，没有固定的语句应用顺序，它是根据PostgreSQL根据其统计数据认为哪个查询会更快执行而决定的，无论是首先执行where还是join。事实上，它甚至可能混合执行两者，首先执行其中一个where，然后执行一个join，然后执行另一个where。
最后，你展示的两个查询足够相似，以至于人类可以轻松地看出它们是相同的，因此不应该让人惊讶PostgreSQL也能够确定它们是相同的。由于查询是相同的，PostgreSQL将在内部将它们重写为相同的查询，然后相应地为它们制定相同的执行计划。
英文:
You can put an EXPLAIN before to see what the execution plan for a particular query is, which will explain how postgres will attempt to execute the query. You could even prepend the query with EXPLAIN ANALYZE to check the performance when the query is executed.
However, it's important to understand that the execution plan of the query is set up with the idea that the query should be correctly executed as quickly as possible. Accordingly there is no set in stone order in which the statements are applied, whether it will do the where or join first only depends on which of the two postgres thinks will be execute faster based on the statistics that it has. In fact, it might even mix it the two, first doing one of the where's then a join and then another where.
Lastly, the two queries you show are similar enough that a human can easily tell they are identical, so it shouldn't be a surprise that postgres can tell they are identical as well. Since the queries are identical postgres will internally rewrite the queries to the same query and then accordingly the make an identical execution plan for them.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论