Exclusion join index use

I’m trying to fetch rows of a table that don’t have an ID in another table. For example, all rows of table a that don’t exist in table b:

> CREATE TABLE a (x INT PRIMARY KEY); CREATE TABLE b (y INT PRIMARY KEY);
CREATE TABLE

An explain shows:

> EXPLAIN SELECT a.* FROM a LEFT JOIN b ON a.x = b.y WHERE b.y IS NULL and a.x = 1;
+-------+--------+-------+-------------+
| Level |  Type  | Field | Description |
+-------+--------+-------+-------------+
|     0 | render |       |             |
|     1 | filter |       |             |
|     2 | join   |       |             |
|     2 |        | type  | left outer  |
|     3 | scan   |       |             |
|     3 |        | table | a@primary   |
|     3 |        | spans | ALL         |
|     3 | scan   |       |             |
|     3 |        | table | b@primary   |
|     3 |        | spans | ALL         |
+-------+--------+-------+-------------+

Here no index is used ever though table a should be restricted to a single row. The b.y IS NULL appears to be preventing an index from being used. Is there a better query to do this that will use an index?

What about

SELECT a.* FROM (SELECT * FROM a WHERE x = 1) AS a EXCEPT (SELECT * FROM b)

?

I think it’s a bug that a restriction on a's PK is not used… @radu?

No the reason why the original query is not optimized is that we do not propagate filter through outer joins.