在Postgres执行计划里面出现两种filter ,容易混淆,这里记录一下:
scan filter: 普通的过滤条件,一般来自与where ...; having ...子句中的条件。
join filter: 多表联结的时候,表与表之间联结时候的过滤条件 ,来自于on子语 。需要注意的是:在外连接的时候,是不能过滤行的, 在内连接的的时候与普通的filter没有区别。
看下官方文档怎么说的:
When dealing with outer joins, you might see join plan nodes with both “Join Filter” and plain “Filter”
conditions attached. Join Filter conditions come from the outer join's ON clause, so a row that fails
the Join Filter condition could still get emitted as a null-extended row.But a plain Filter condition is
applied after the outer-join rules and so acts to remove rows unconditionally. In an inner join there is
no semantic difference between these types of filters.
举个例子:
create table test_left
( id int, name varchar(50)