From: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Unify "In" Sublink to EXIST Sublink for better optimize opportunity |
Date: | 2022-10-10 00:40:34 |
Message-ID: | CAKU4AWogMytb4EDJcVjZFuRMj1-TbsrYio8z2X0NfP4VkqbOsQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi:
On Thu, Oct 6, 2022 at 3:24 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>
> Due to the implementation of convert_ANY_sublink_to_join, we have
> limitations 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 like
>
> SELECT * FROM t1 WHERE
> a IN (SELECT * FROM t2 WHERE t2.b > t1.b);
>
> can be converted to
>
> SELECT * FROM t1 WHERE
> EXISTS (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);
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;
master: 892.902 ms
patched: 56.08 ms
>
Patch attached, any feedback is welcome.
--
Best Regards
Andy Fan
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Pulling-up-direct-correlated-ANY_SUBLINK.patch | application/octet-stream | 18.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | bt22nakamorit | 2022-10-10 02:30:03 | Re: ps command does not show walsender's connected db |
Previous Message | Tom Lane | 2022-10-09 23:12:50 | Re: create subscription - improved warning message |