Hi
For a left join with a subquery on the right-hand side, that subquery doesn't seem to be flattened. This seems to work well with an inner join. I have attached a reprex. It creates two tables with $n rows and one ID column each (200k is enough to show substantial slowdown), and joins them with and without subqueries. The third example seems to create a suboptimal query plan and takes much longer than necessary to run. The output on my system is below the message. Thanks for your attention. Best regards Kirill 1|0|0|SCAN TABLE t1 0|0|0|SCAN SUBQUERY 1 1 real 0m0.003s user 0m0.000s sys 0m0.000s 1|0|0|SCAN TABLE t1 0|0|0|SCAN SUBQUERY 1 1 real 0m0.003s user 0m0.000s sys 0m0.000s 2|0|0|SCAN TABLE t2 1|0|0|SCAN TABLE t1 1|1|1|SEARCH SUBQUERY 2 AS zzz2 USING AUTOMATIC COVERING INDEX (a=?) 0|0|0|SCAN SUBQUERY 1 1 real 0m0.277s user 0m0.264s sys 0m0.012s 1|0|0|SCAN TABLE t1 1|1|1|SEARCH TABLE t2 USING COVERING INDEX sqlite_autoindex_t2_1 (a=?) 0|0|0|SCAN SUBQUERY 1 1 real 0m0.003s user 0m0.000s sys 0m0.000s
