Re: Quals not pushed down into lateral

On Thu, Apr 13, 2017 at 01:39:07PM -0700, Andres Freund wrote:
> On 2017-04-13 16:34:12 -0400, Robert Haas wrote:
> > On Thu, Mar 16, 2017 at 4:45 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > During citus development we noticed that restrictions aren't pushed down
> > > into lateral subqueries, even if they semantically could. For example,
> > > in this dumbed down example:
> > >
> > > postgres[31776][1]=# CREATE TABLE t_2(id serial primary key);
> > > postgres[31776][1]=# CREATE TABLE t_1(id serial primary key);
> > >
> > > Comparing:
> > >
> > > postgres[31776][1]=# EXPLAIN SELECT * FROM t_1 JOIN (SELECT * FROM t_2 GROUP BY id) s ON (t_1.id = s.id) WHERE t_1.id = 3;
> > > postgres[31776][1]=# EXPLAIN SELECT * FROM t_1, LATERAL (SELECT * FROM t_2 WHERE t_1.id = t_2.id GROUP BY id) s WHERE t_1.id = 3;
> >
> > Interesting. That does seem like we are missing a trick.
>
> Yea.
>
> > Not exactly related, but I think we need to improve optimization
> > around CTEs, too. AFAICT, what we've got right now, almost everybody
> > hates.
>
> That's certainly an issue, but it's a lot harder to resolve because
> we've, for years, told people to intentionally use CTEs as optimization
> barriers :(

If we can get better performance by removing the barriers, we can
certainly explain the new hack, assuming there is or needs to be
one, in the release notes. We haven't promised to keep the current
behavior forever, nor should we.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate