Обсуждение: pgsql: Allow simplification of EXISTS() subqueries containing LIMIT.

Поиск
Список
Период
Сортировка

pgsql: Allow simplification of EXISTS() subqueries containing LIMIT.

От
Tom Lane
Дата:
Allow simplification of EXISTS() subqueries containing LIMIT.

The locution "EXISTS(SELECT ... LIMIT 1)" seems to be rather common among
people who don't realize that the database already performs optimizations
equivalent to putting LIMIT 1 in the sub-select.  Unfortunately, this was
actually making things worse, because it prevented us from optimizing such
EXISTS clauses into semi or anti joins.  Teach simplify_EXISTS_query() to
suppress constant-positive LIMIT clauses.  That fixes the semi/anti-join
case, and may help marginally even for cases that have to be left as
sub-SELECTs.

Marti Raudsepp, reviewed by David Rowley

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/b62f94c60386796fd88256c5b7b1e8301c345166

Modified Files
--------------
src/backend/optimizer/plan/subselect.c  |   52 +++++++++++++++++++++++++------
src/test/regress/expected/subselect.out |   40 ++++++++++++++++++++++++
src/test/regress/sql/subselect.sql      |   13 ++++++++
3 files changed, 96 insertions(+), 9 deletions(-)