pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) #





view thread (11+ messages)  latest in thread

Message-ID: <m0ziKjE-000EBYC@orion.SAPserv.Hamburg.dsh.de>
Permalink:  ../m0ziKjE-000EBYC@orion.SAPserv.Hamburg.dsh.de/
Also on:    postgresql.org/message-id/m0ziKjE-000EBYC@orion.SAPserv.Hamburg.dsh.de

 · 

reply

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