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
