Vadim wrote:
> This thing would also handled by subqueries in FROM!
> Having support in planner/executor for queries like this:
>
> select * from A, (select c, max(d) as m from B group by c) SQ
> where SQ.c = A.x and SQ.m = A.y
>
> rule system will be able to put _any_ VIEW' query into
> FROM clause...
Possible - but IMHO the wrong thing to do. As it is now for a
view that has no aggregate, the rule system rewrites the
query to something that is the same as if the user resolved
the view definition by hand and used all the real tables
instead. Have the following:
CREATE TABLE t1 (a int4, b int4);
CREATE TABLE t2 (a int4, c int4);
CREATE TABLE t3 (a int4);
CREATE VIEW v1 AS SELECT t1.a, t1.b, t2.c
FROM t1, t2 WHERE t1.a = t2.a;
Now do a
SELECT t3.a, v1.b, v1.c FROM t3, v1
WHERE t3.a = v1.a;
The current rewrite system builds a querytree that is exactly
that what would have been produced by the parser if you had
typed
SELECT t3.a, t1.b, t2.c FROM t3, t1, t2
WHERE t3.a = t1.a AND t1.a = t2.a;
Now the planner/optimizer has _ALL_ the tables that need to
be scanned and _ALL_ the qualifications in _ONE_ querytree.
It is the job of the optimizer to decide which is the best
join path for this access. To make a good decision, it needs
all this information plus the VACUUM statistics.
If we put any view into a subquery RTE, we force the planner
to materialize the view and do a nestloop over t3 and
materialized v1 where possibly using t1 or t2 as the
outermost scanrelation would be better.
Stonebraker & Co where absolutely right when they spoke about
productional rule systems. And what PostgreSQL does now is
how I understood them.
"Production rule systems are conceptually simple, but
there are many subtle points involved in actually using
them."
-- Stonebraker
I think the different grouping requirements for subsets of
data when using aggregate columns in views is just one of the
the problems he addressed with the above statement.
We should build a subquery RTE only if Query->hasAggs is
true.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
