Re: [PATCH] Simplify EXISTS subqueries containing LIMIT

On Fri, Oct 3, 2014 at 10:41 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:

> Hi list,
>
> Attached patch allows semijoin/antijoin/hashed SubPlan optimization
> when an EXISTS subquery contains a LIMIT clause with a positive
> constant. It seems to be a fairly common meme to put LIMIT 1 into
> EXISTS() subqueries, and it even makes sense when you're not aware
> that the database already does this optimization.
>
>
I had a quick look at this, and the code looks fairly simple. Although,
I've got mixed feelings about it;

I guess there's not really any real performance penalty in planning time
for everyone else who does not put LIMIT clauses into their exists
subqueries, so maybe it's worth it as it seems there could still be a few
people out there suffering from this, but at the same time, the argument
for this would have been much stronger if anti join support had just been
added last week. It's been quite a few years now and the argument for this
must be getting weaker with every release.

I think I'm leaning towards a +1 on this as it seems a shame for people who
have no control over the queries sent to their database to have to be
excluded from the benefits of semi join and anti join.

Regards

David Rowley

Do we want this?
>
> It has come up in #postgresql, and at twice times on mailing lists:
> http://www.postgresql.org/message-id/53279529.2070902@freemail.hu
> http://www.postgresql.org/message-id/50A36820.4030400@pingpong.net
>
> And there may even be good reasons, such as writing performant
> portable SQL code for Other Databases:
> https://dev.mysql.com/doc/refman/5.1/en/optimizing-subqueries.html
>
>