[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: Missed query planner optimization: `n in (select q)` -> `n in (q)`
From:       "David G. Johnston" <david.g.johnston () gmail ! com>
Date:       2022-06-12 23:17:14
Message-ID: CAKFQuwb49d78mnp7uyJPeX-FiPK9saCG8bqKZP90kryoAuky5A () mail ! gmail ! com
[Download RAW message or body]

On Sun, Jun 12, 2022 at 2:47 PM Josh <postgres@sirjosh3917.com> wrote:

>
> This was only possible because I was dealing with arrays though, and an
> operation such as `in (select unnest...)` can be easily converted to `=
> any(...)`. However for the general case,


In the general case you don't have subqueries inside join conditions.


> I believe an optimization in this area may provide benefit as there may
> exist a circumstance that does not have an alternative to a sub-query
> select (`= any()` was my alternative)


I think we'd want a concrete example of a non-poorly written query (or at
least a poorly written one that, say, is generated by a framework, not just
inexperienced human SQL writers) before we'd want to even entertain
spending time on something like this.


> - Is this an issue that should be fixed?


Probably not worth the effort.

I'm fascinated by the work y'all do, and submitting a patch to Postgres
> that makes it into production would make my week.
>
>
Maybe you'll find almost as much good is done helping others get their
patches committed.  There are many in need of reviewers.

https://commitfest.postgresql.org/

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><span \
style="font-family:Arial,Helvetica,sans-serif">On Sun, Jun 12, 2022 at 2:47 PM Josh \
&lt;<a href="mailto:postgres@sirjosh3917.com">postgres@sirjosh3917.com</a>&gt; \
wrote:</span><br></div></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><br> This was only possible because I was dealing \
with arrays though, and an operation such as `in (select unnest...)` can be easily \
converted to `= any(...)`. However for the general \
case,</blockquote><div><br></div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">In the general case you don&#39;t have \
subqueries inside join conditions.</div><div>  </div><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">I believe an optimization in this area may provide \
benefit as there may exist a circumstance that does not have an alternative to a \
sub-query select (`= any()` was my alternative)</blockquote><div><br></div><div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">I think we&#39;d \
want a concrete example of a non-poorly written query (or at least a poorly written \
one that, say, is generated by a framework, not just inexperienced human SQL writers) \
before we&#39;d want to even entertain spending time on something like \
this.</div></div><div>  </div><blockquote class="gmail_quote" style="margin:0px 0px \
                0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
- Is this an issue that should be fixed?</blockquote><div><br></div><div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">Probably not \
worth the effort.</div></div><div><br></div><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"> I&#39;m fascinated by the work y&#39;all do, and \
submitting a patch to Postgres that makes it into production would make my \
week.<br><br></blockquote><div><br></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">Maybe you&#39;ll find almost as much \
good is done helping others get their patches committed.   There are many in need of \
reviewers.</div><br></div><div><a \
href="https://commitfest.postgresql.org/">https://commitfest.postgresql.org/</a></div><div><br></div><div><br></div></div></div>




[prev in list] [next in list] [prev in thread] [next in thread]