pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNested Views take forever
3+ messages / 3 participants
[nested] [flat]
* Nested Views take forever
@ 1999-11-11 23:10 Zot O'Connor <zot@zotconsulting.com>
0 siblings, 1 reply; 3+ messages in thread
From: Zot O'Connor @ 1999-11-11 23:10 UTC (permalink / raw)
To: postgres sql <pgsql-sql@hub.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Nested Views take forever
@ 1999-11-12 00:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Zot O'Connor <zot@zotconsulting.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 1999-11-12 00:34 UTC (permalink / raw)
To: Zot O'Connor <zot@zotconsulting.com>; +Cc: postgres sql <pgsql-sql@hub.org>
"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 WHERE
subcat.scatscat = depth1.scatval;
CREATE VIEW depth3 AS SELECT ... FROM subcat, depth2 WHERE
subcat.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Nested Views take forever
@ 1999-11-12 07:42 marten@feki.toppoint.de
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: marten@feki.toppoint.de @ 1999-11-12 07:42 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: zot@zotconsulting.com; pgsql-sql@hub.org
>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-11-12 07:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-11 23:10 Nested Views take forever Zot O'Connor <zot@zotconsulting.com>
1999-11-12 00:34 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-11-12 07:42 ` marten@feki.toppoint.de
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