Quals not pushed down into lateral
Hi,
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;
┌─────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop (cost=0.31..16.37 rows=1 width=8) │
│ -> Index Only Scan using t_1_pkey on t_1 (cost=0.15..8.17 rows=1 width=4) │
│ Index Cond: (id = 3) │
│ -> Group (cost=0.15..8.17 rows=1 width=4) │
│ Group Key: t_2.id │
│ -> Index Only Scan using t_2_pkey on t_2 (cost=0.15..8.17 rows=1 width=4) │
│ Index Cond: (id = 3) │
└─────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)
with:
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;
┌─────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop (cost=0.31..16.37 rows=1 width=8) │
│ -> Index Only Scan using t_1_pkey on t_1 (cost=0.15..8.17 rows=1 width=4) │
│ Index Cond: (id = 3) │
│ -> Group (cost=0.15..8.17 rows=1 width=4) │
│ Group Key: t_2.id │
│ -> Index Only Scan using t_2_pkey on t_2 (cost=0.15..8.17 rows=1 width=4) │
│ Index Cond: (id = t_1.id) │
└─────────────────────────────────────────────────────────────────────────────────────┘
it's noticeable that the former has id = 3 pushed down into both
relations index scans, whereas the latter doesn't.
This seems like a worthwhile future optimization opportunity.
I've not looked into this in any detail, but the proximate source is
that set_subquery_pathlist() doesn't see any baserstrictinfos to push
down. Which makes sense, because t_1.id = t_2.id isn't "visible" (in
the sense of deconstruct_jointree dealing with it) to the outside.
It seems possible to look into rel->lateral_vars, check whether that's
member of some equivclass, and then push the relevant equivalences down
(after taking care that the Var from the outside is known as a Param on
the inside).
I'm not planning to work on this anytime soon, but I thought it'd be
useful to have a searchable reference point about the topic. If
somebody wants to work on it...
Greetings,
Andres Freund
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Mar 16, 2017 at 4:45 AM, Andres Freund <andres@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.
Not exactly related, but I think we need to improve optimization
around CTEs, too. AFAICT, what we've got right now, almost everybody
hates.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2017-04-13 16:34:12 -0400, Robert Haas wrote:
On Thu, Mar 16, 2017 at 4:45 AM, Andres Freund <andres@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 :(
- Andres
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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@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