Re: [v9.2] Fix Leaky View Problem

On Sun, Oct 16, 2011 at 4:46 AM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)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