Обсуждение: Nested Views take forever

I have a table subcat with can link to itself.  scatscat can point to 0,
or aan id (scatval).

I wanted to be to get to the various depths, so I used views:


CREATE VIEW depth1 AS SELECT scatval AS d1_scatval ,scatscat AS
d1_scatscat, scatdescr AS D1_scatdescr  from subcat where scatscat = 0;
CREATE VIEW depth2 AS SELECT scatval AS d2_scatval ,scatscat AS
d2_scatscat, scatdescr AS D2_scatdescr from subcat where scatscat in
(SELECT d1_scatval FROM depth1);
CREATE VIEW depth3 AS SELECT scatval AS d3_scatval ,scatscat AS
d3_scatscat, scatdescr AS D3_scatdescr from subcat where scatscat in
(SELECT d2_scatval FROM depth2);

"select * from depth2" works fine, but select * from depth3 takes a long
time.

There are 233 subcats, 20 in depth1, 37, in depth2, and 179 in depth3.

when I dselect from depth, it is fast, depth2 is a bit slower, but depth
3 can be 45 seconds or more.

It seems obvious that it is recreating the VIEWs each time, even though
nothing has changed.

Is there anyway to make the views more persistent or should I just make
tables again?

consup=> EXPLAIN SELECT COUNT(*) from depth3;
NOTICE:  QUERY PLAN:

Aggregate  (cost=11.69 rows=233 width=4) ->  Seq Scan on subcat  (cost=11.69 rows=233 width=4)       SubPlan         ->
Seq Scan on subcat  (cost=11.69 rows=233 width=4)               SubPlan                 ->  Seq Scan on subcat
(cost=11.69rows=6 width=4)
 


I have an index on the values:
create index scat_ndx  on subcat (scatval);
create index scatc_ndx on subcat (scatcat);

The subcat table:

create table subcat (        scatval   integer DEFAULT NEXTVAL ('scat_seq'),       scatcat   integer NOT NULL,
scatscat  integer NOT NULL,       scatzid   integer,       scatlid   integer,       scatlogo  varchar,       scatlogoh
integer,      scatlogow integer,       scatdescr varchar,       scatback  varchar,       scatbg    varchar,
scatlink varchar,       scatvlink varchar,       scatalink varchar,       scatbanr  varchar,       scatbanrh integer,
   scatbanrw integer,       scatsku   varchar,       scattext  varchar,       scatmast  varchar(8103),       scattmpl
varchar,      scatsort  varchar,       scatfree  varchar
 
);

Am I wrong to use VIEWs here?

-- 
Zot O'Connor

www.ZotConsulting.com
www.WhiteKnightHackers.com

"Zot O'Connor" <zot@zotconsulting.com> writes:
> consup=> EXPLAIN SELECT COUNT(*) from depth3;
> NOTICE:  QUERY PLAN:
> Aggregate  (cost=11.69 rows=233 width=4)
>   ->  Seq Scan on subcat  (cost=11.69 rows=233 width=4)
>         SubPlan
>           ->  Seq Scan on subcat  (cost=11.69 rows=233 width=4)
>                 SubPlan
>                   ->  Seq Scan on subcat  (cost=11.69 rows=6 width=4)

The problem here is not views per se, it's that WHERE x in (sub-select)
is not a very efficient construct --- it basically always generates a
nested-loop plan.  What you've got above is O(N^3) for an N-tuple table.

Try something like this instead:

CREATE VIEW depth2 AS SELECT ... FROM subcat, depth1 WHEREsubcat.scatscat = depth1.scatval;

CREATE VIEW depth3 AS SELECT ... FROM subcat, depth2 WHEREsubcat.scatscat = depth2.scatval;

Given indexes on scatscat and scatval, I'd expect this to produce a
merge-join plan, which should be reasonably quick --- better than
O(N^2) or O(N^3) anyway.

There's been some talk of reimplementing WHERE ... IN ... so that it
does something intelligent without help, but there are a lot of
higher-priority problems on the TODO list...
        regards, tom lane

> 
> There's been some talk of reimplementing WHERE ... IN ... so that it
> does something intelligent without help, but there are a lot of
> higher-priority problems on the TODO list...
> 
>             regards, tom lane
This only affect statement, where the "inner" select accesses some
stuff of the "outer" select statement ? Actually a statement like:
select * from table where index in  (select foreign-key from table2 where table2.name = 'Hallo')

would not have such problems ? Correct ?

Marten