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



view thread (3+ messages)  latest in thread

Message-ID: <382B4CD1.E7EB3C79@zotconsulting.com>
Permalink:  ../382B4CD1.E7EB3C79@zotconsulting.com/
Also on:    postgresql.org/message-id/382B4CD1.E7EB3C79@zotconsulting.com

 · 

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: 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