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 <and...@anarazel.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers