From: jwieck@debis.com
To: vadim@krs.ru
Cc: jwieck@debis.com
Cc: s-fery@kkt.sote.hu
Cc: pgsql-sql@postgreSQL.org
Cc: hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] cursor and update + view
Date: Tue, 24 Nov 1998 16:50:11 +0100 (MET)
Message-ID: <m0ziKjE-000EBYC@orion.SAPserv.Hamburg.dsh.de> (raw)
In-Reply-To: <365ABC07.9534B144@krs.ru>
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) #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jwieck@debis.com, vadim@krs.ru, s-fery@kkt.sote.hu, hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] cursor and update + view
In-Reply-To: <m0ziKjE-000EBYC@orion.SAPserv.Hamburg.dsh.de>
References: <365ABC07.9534B144@krs.ru> <m0ziKjE-000EBYC@orion.SAPserv.Hamburg.dsh.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox