On Wed, Oct 19, 2011 at 6:35 AM, Kohei KaiGai <kai...@kaigai.gr.jp> wrote: > 2011/10/19 Tom Lane <t...@sss.pgh.pa.us>: >> Robert Haas <robertmh...@gmail.com> writes: >>> On Sun, Oct 16, 2011 at 4:46 AM, Kohei KaiGai <kai...@kaigai.gr.jp> wrote: >>>> 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. >> >> No, the collapse_limit variables are entirely unrelated to subquery >> flattening, or to qual pushdown for that matter. They only restrict the >> number of join paths we consider. And we will attempt to push down >> quals into an unflattened subquery, too, if it looks safe. See >> subquery_is_pushdown_safe, qual_is_pushdown_safe, etc in allpaths.c. >> > I tried to observe the behavior with a bit modification of is_simple_subquery > that become to return 'false' always. > (It is a simulation if and when a view with security_barrier would be given.) > > The expected behavior is to keep sub-query without flatten. > However, the externally provided qualifiers are correctly pushed down. > > Do we need to focus on the code around above functions rather than > distribute_qual_to_rels, to prevent undesirable pushing-down across > security barrier? > > postgres=# CREATE VIEW v1 AS SELECT * FROM t1 WHERE a > 100; > CREATE VIEW > postgres=# CREATE VIEW v2 AS SELECT * FROM t2 JOIN t3 ON x = s; > CREATE VIEW > postgres=# EXPLAIN SELECT * FROM v1 WHERE b = 'bbb'; > QUERY PLAN > ---------------------------------------------------- > Seq Scan on t1 (cost=0.00..28.45 rows=2 width=36) > Filter: ((a > 100) AND (b = 'bbb'::text)) > (2 rows) > postgres=# EXPLAIN SELECT * FROM v2 WHERE t = 'ttt'; > QUERY PLAN > ---------------------------------------------------------------- > Hash Join (cost=25.45..52.73 rows=37 width=72) > Hash Cond: (t2.x = t3.s) > -> Seq Scan on t2 (cost=0.00..22.30 rows=1230 width=36) > -> Hash (cost=25.38..25.38 rows=6 width=36) > -> Seq Scan on t3 (cost=0.00..25.38 rows=6 width=36) > Filter: (t = 'ttt'::text) > (6 rows)
Well, there's clearly some way to prevent pushdown from happening, because sticking a LIMIT in there does the trick... -- 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
