Thread: ERROR: DefineQueryRewrite: rule plan string too big.

ERROR:  DefineQueryRewrite: rule plan string too big.

i get this error trying to build this massive view to join about 15 tables
of data together.  the error sounds reasonable enough, but i'm getting
confused trying to break it down into smaller views and then combining
them.

the view basically just pulls together 15 tables of data while renaming
columns to be meaningful when viewed together, and of course associating
foreign keys with their primaries.

here is the query if it should prove useful:
create view allinfo as select b.name as building_name,b.description as
building_description,b.location as building_location,b.address as
building_address,f.location as floor,r.name as room_name,r.location as
room_location,r.dept as room_department,rt.name as room_type,h.mac as
mac,h.name as hardware_name,h.ip as ip,h.mutag as mutag,rack.location as
rack,rack.description as rack_description,h.pos as pos,h.serial as
serial,h.inuse as inuse,m.name as model_name,m.description as
model_description,m.model as model,c.name as model_class,manf.name as
manufacturer from building as b,floor as f,room as r,roomtype as
rt,hardware as h,dept as d,model as m,class as c,manufacturer as manf,rack
where b.number=f.building and f.number=r.floor and r.number=rack.room and
rack.number=h.rack and m.class=c.number and m.manufacturer=manf.number and
r.dept=d.number;

how would be the best way to build smaller views and combine them into
larger views?

thanks.

____________________}John Flinchbaugh{______________________
| -> glynis@hjsoft.com <-             john@cs.millersv.edu |
|    glynis@netrax.net      http://www.hjsoft.com/~glynis/ |
~~Powered by Linux: Reboots are for hardware upgrades only~~

At 23:45 +0300 on 23/07/1999, John M. Flinchbaugh wrote:


> create view allinfo as select b.name as building_name,b.description as
> building_description,b.location as building_location,b.address as
> building_address,f.location as floor,r.name as room_name,r.location as
> room_location,r.dept as room_department,rt.name as room_type,h.mac as
> mac,h.name as hardware_name,h.ip as ip,h.mutag as mutag,rack.location as
> rack,rack.description as rack_description,h.pos as pos,h.serial as
> serial,h.inuse as inuse,m.name as model_name,m.description as
> model_description,m.model as model,c.name as model_class,manf.name as
> manufacturer from building as b,floor as f,room as r,roomtype as
> rt,hardware as h,dept as d,model as m,class as c,manufacturer as manf,rack
> where b.number=f.building and f.number=r.floor and r.number=rack.room and
> rack.number=h.rack and m.class=c.number and m.manufacturer=manf.number and
> r.dept=d.number;

First, I do believe that in the FROM clause, you don't use "as" to create
table aliases. This is done only in the target list. For table aliases you
just put the new name:

FROM building b, floor f, room r, roomtype rt, hardware h, dept d etc.

Now, here is a (braindead) workaround which doesn't require you to plan a
lot, and may help you reduce this plan into two views.

View number 1 - this same view, only with short field names:

CREATE VIEW internal1 AS
SELECT b.name as f1, b.description as f2, ....
FROM ....
WHERE ....;

View number 2 - straight select from internal1, only renaming the fields to
their longer names:

CREATE VIEW allinfo AS
SELECT f1 as building_name, f2 as building_description, f3 as ...
FROM internal1;

This may get you just under the threshold and save you a lot of thinking.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Herouth Maoz <herouth@oumail.openu.ac.il> writes:
> At 23:45 +0300 on 23/07/1999, John M. Flinchbaugh wrote:
>> [ how to avoid "rule plan string too big" ? ]

> First, I do believe that in the FROM clause, you don't use "as" to create
> table aliases. This is done only in the target list.

"AS" is an allowed noise word in FROM, see the production for table_expr
in gram.y.  However, this sort of hacking on the text source of the
query isn't going to make much if any difference to the length of the
resulting parse tree, which is what John's actually running into trouble
with.  The real problem is that a parse tree for a ten-way join with
this many output columns just plain has a lot of nodes :-(, and the way
that they're being stored in rules is quite verbose.  (I think Jan is
going to introduce compression of rule plan strings shortly, but that's
not going to help John right now.)

He might have some luck by splitting the query into two subqueries,
one that joins half the tables and the other one joining the other half,
then a top query that joins those two.  (The trick here is figuring
out how to split the tables into two groups that can be joined without
reference to the other group and without producing too many useless
tuples.)  The top query is still going to be tight, because the number
of targetlist nodes (output columns) won't change, but reducing the
number of tables it references to two views and simplifying its WHERE
condition down to one or so clauses will both help eliminate nodes.

I originally thought that this wouldn't work because the final parsetree
for the top query would incorporate the other two parsetrees and still
be too big.  But it looks like it doesn't --- when I experimented just
now, the stored parsetree for a view referencing another view treated
the sub-view as just a table with no inner structure.  I suppose the
expansion doesn't happen until plan/optimize time.
        regards, tom lane

Tom Lane wrote:

> I originally thought that this wouldn't work because the final parsetree
> for the top query would incorporate the other two parsetrees and still
> be too big.  But it looks like it doesn't --- when I experimented just
> now, the stored parsetree for a view referencing another view treated
> the sub-view as just a table with no inner structure.  I suppose the
> expansion doesn't happen until plan/optimize time.

    It  happens  during  rewrite  time.  The querytree stored for
    rules is just the UNrewritten output of  the  parser.  That's
    why  the rewriter is recursively calling itself with it's own
    output.

    So splitting up such a huge view into a  tree  of  views  (if
    possible)  is  a solution that must not result in a different
    final querytree (after applying  all  view  rules).  It  just
    takes  a little more time for the rewrite step - but I cannot
    estimate how much more.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

On Sun, 25 Jul 1999, Tom Lane wrote:
> > At 23:45 +0300 on 23/07/1999, John M. Flinchbaugh wrote:
> >> [ how to avoid "rule plan string too big" ? ]
> He might have some luck by splitting the query into two subqueries,
> one that joins half the tables and the other one joining the other half,
> then a top query that joins those two.  (The trick here is figuring
> out how to split the tables into two groups that can be joined without
> reference to the other group and without producing too many useless
> tuples.)  The top query is still going to be tight, because the number
> of targetlist nodes (output columns) won't change, but reducing the
> number of tables it references to two views and simplifying its WHERE
> condition down to one or so clauses will both help eliminate nodes.

i broke that huge view into a bunch of smaller ones successfully, but the
top view which pulled together 2 smaller views (which each pulled together
smaller parts,etc), and i got the same error.  it seems to still get hung
up on the number of columns the view was returning, so i had to drop a few
of the less useful columns to get it to create the view successfully.

____________________}John Flinchbaugh{______________________
| -> glynis@hjsoft.com <-             john@cs.millersv.edu |
|    glynis@netrax.net      http://www.hjsoft.com/~glynis/ |
~~Powered by Linux: Reboots are for hardware upgrades only~~