Re: [PATCH] Simplify EXISTS subqueries containing LIMIT
| От | David Rowley |
|---|---|
| Тема | Re: [PATCH] Simplify EXISTS subqueries containing LIMIT |
| Дата | |
| Msg-id | CAApHDvpg6T3n_sDzLtEiFNihaD-GzSW-wFRpD-DvvjJw=EeEpg@mail.gmail.com обсуждение исходный текст |
| Ответ на | [PATCH] Simplify EXISTS subqueries containing LIMIT (Marti Raudsepp <marti@juffo.org>) |
| Ответы |
Re: [PATCH] Simplify EXISTS subqueries containing LIMIT
|
| Список | pgsql-hackers |
On Fri, Oct 3, 2014 at 10:41 AM, Marti Raudsepp <marti@juffo.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
В списке pgsql-hackers по дате отправления: