Re: To use a VIEW or not to use a View.....

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> ... but this is a general transitivity constraint AFAIK, not
> one actually to do with views (ie, if you wrote out the query without a
> view, you can run into the same issue). It's somewhat easier to run into
> the case with views and the effect may be exasperated by views, but it's
> a general condition.

Right. Views are just macros --- they don't in themselves affect the
planner's ability to generate a good plan. But they make it easier to
generate baroque queries without thinking much about what you're doing,
and in complex queries the planner doesn't always make the deductions
and simplifications that are obvious to a human.

> For example:
> create table a(a int);
> create table c(a int);

> sszabo=# explain select * from a join c using (a) where a=3;
> QUERY PLAN
> -------------------------------------------------------------
> Hash Join (cost=1.01..26.08 rows=6 width=8)
> Hash Cond: ("outer".a = "inner".a)
> -> Seq Scan on c (cost=0.00..20.00 rows=1000 width=4)
> -> Hash (cost=1.01..1.01 rows=1 width=4)
> -> Seq Scan on a (cost=0.00..1.01 rows=1 width=4)
> Filter: (a = 3)
> (6 rows)

> The filter is applied only to a. So, if you really wanted the
> c.a=3 condition to be applied for whatever reason you're out of
> luck.

FWIW, CVS tip is brighter: the condition does propagate to both relations.

Hash Join (cost=22.51..45.04 rows=1 width=8)
Hash Cond: ("outer".a = "inner".a)
-> Seq Scan on a (cost=0.00..22.50 rows=5 width=4)
Filter: (a = 3)
-> Hash (cost=22.50..22.50 rows=5 width=4)
-> Seq Scan on c (cost=0.00..22.50 rows=5 width=4)
Filter: (3 = a)

The reason this is useful is that (a) fewer rows need to be joined,
and (b) we may be able to make effective use of indexes on both tables.

regards, tom lane