sequelize
Patch 5 repro
"create more clear case": https://github.com/sequelize/sequelize-sscce/actions/runs/6290931879/job/17078814349#step:7:83 EXISTS is way slower
"Maybe we have special case?": https://github.com/sequelize/sequelize-sscce/actions/runs/6290993557/job/17078936600#step:7:83 EXISTS is way faster
The difference between the two commits is that the bars is being limited to a smaller set of records
where: { id: { [Op.gt]: 123 } } VS where: { id: { [Op.between]: [2345, 8678] } }
This indicates that EXISTS is only faster if the inner subquery (bars) returns same amount or less rows than then outer query (foos).
Also interestingly, postgres 9.5, 10, 11 all take around 500-700ms with EXISTS and 2000ms with IS NOT NULL to execute "Maybe its our postgres version?". But when we try the same case on postgres 12 or 16, the EXISTS version of the query drops down to 20ms. There were clearly some optimizations done in postgres 12.