On Sun, Oct 16, 2011 at 4:46 AM, Kohei KaiGai <kai...@kaigai.gr.jp> wrote: > Hi Robert, > > I'm a bit confusing about this sentence. > >> If you can make this work, I think it could be a pretty sweet plannner >> optimization even apart from the implications for security views. >> Consider a query of this form: >> >> A LEFT JOIN B LEFT JOIN C >> >> where B is a view defined as: >> >> B1 JOIN B2 JOIN B3 LEFT JOIN B4 LEFT JOIN B5 >> >> Now let's suppose that from_collapse_limit/join_collapse_limit are set >> low enough that we decline to fold these subproblems together. If >> there happens to be a qual B.x = 1, where B.x is really B1.x, then the >> generated plan sucks, because it will basically lose the ability to >> filter B1 early, very possibly on, say, a unique index. Or at least a >> highly selective index. >> > > I tried to reproduce the scenario with enough small from/join_collapse_limit > (typically 1), but it allows to push down qualifiers into the least scan plan.
Hmm, you're right. LIMIT 1000000000 prevents qual pushdown, but hitting from_collapse_limit/join_collapse_limit apparently doesn't. I could have sworn I've seen this work the other way, but I guess not. > E.g) > mytest=# SET from_collapse_limit = 1; > mytest=# SET join_collapse_limit = 1; > mytest=# CREATE VIEW B AS SELECT B1.* FROM B1,B2,B3 WHERE B1.x = B2.x > AND B2.x = B3.x; > mytest=# EXPLAIN SELECT * FROM A,B,C WHERE A.x=B.x AND B.x=C.x AND > f_leak(B.y); This I wouldn't expect to have any effect anyway, because you're using the ad-hoc join syntax rather than explicit join syntax. But I tried it with explicit join syntax and it seems to only constrain the join order, not prevent qual pushdown. > I agree with the following approach to tackle this problem in 100%. > However, I'm unclear how from/join_collapse_limit affects to keep > sub-queries unflatten. It seems to me it is determined based on > the result of is_simple_subquery(). I think you are right, but I'm not sure it's right to hack is_simple_subquery() directly. Perhaps what we want to do is modify pull_up_subquery()? -- 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
