From: Zot O'Connor <zot@zotconsulting.com>
To: postgres sql <pgsql-sql@hub.org>
Subject: Nested Views take forever
Date: Thu, 11 Nov 1999 15:10:09 -0800
Message-ID: <382B4CD1.E7EB3C79@zotconsulting.com> (raw)
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.69 rows=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
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: zot@zotconsulting.com, pgsql-sql@hub.org
Subject: Re: Nested Views take forever
In-Reply-To: <382B4CD1.E7EB3C79@zotconsulting.com>
References: <382B4CD1.E7EB3C79@zotconsulting.com>
* 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