Re: Unify "In" Sublink to EXIST Sublink for better optimize opportunity
| От | Andy Fan |
|---|---|
| Тема | Re: Unify "In" Sublink to EXIST Sublink for better optimize opportunity |
| Дата | |
| Msg-id | CAKU4AWogMytb4EDJcVjZFuRMj1-TbsrYio8z2X0NfP4VkqbOsQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Unify "In" Sublink to EXIST Sublink for better optimize opportunity (Andy Fan <zhihui.fan1213@gmail.com>) |
| Список | pgsql-hackers |
Hi:
On Thu, Oct 6, 2022 at 3:24 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
Due to the implementation of convert_ANY_sublink_to_join, we havelimitations below, which has been discussed at [1] [2].if (contain_vars_of_level((Node *) subselect, 1))
return NULL;I'm thinking if we can do the ${subject}. If so, the query likeSELECT * FROM t1 WHEREa IN (SELECT * FROM t2 WHERE t2.b > t1.b);can be converted toSELECT * FROM t1 WHEREEXISTS (SELECT * FROM t2 WHERE t2.b > t1.b AND t1.a = t2.a);
I have coded this and tested my idea, here are some new findings: 1). Not all the
TargetEntry->expr can be used as qual, for example: WindowFunc, AggFunc, SRFs.
2). For simple correlated EXISTS query, the current master code also tries to transform it
to IN format and implement it by hashing (make_subplan). So there is no need to
convert an IN query to EXISTS query if the sublink can be pulled up already,
which means this patch should only take care of !contain_vars_of_level((Node *) subselect, 1).
Note the changes of postgres_fdw.out are expected. The 'a' in foreign_tbl has varlevelsup = 1;
SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl);
Note the changes of postgres_fdw.out are expected. The 'a' in foreign_tbl has varlevelsup = 1;
SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl);
Here is some performance testing for this patch:
select * from tenk1 t1
where hundred in (select hundred from tenk2 t2
where t2.odd = t1.odd
and even in (select even from tenk1 t3
where t3.fivethous = t2.fivethous))
and even > 0;
where hundred in (select hundred from tenk2 t2
where t2.odd = t1.odd
and even in (select even from tenk1 t3
where t3.fivethous = t2.fivethous))
and even > 0;
master: 892.902 ms
patched: 56.08 ms
Patch attached, any feedback is welcome.
Best Regards
Andy Fan
Вложения
В списке pgsql-hackers по дате отправления: