Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA42645 for ; Thu, 11 Nov 1999 19:34:47 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id TAA21838; Thu, 11 Nov 1999 19:34:07 -0500 (EST) To: "Zot O'Connor" cc: postgres sql Subject: Re: [SQL] Nested Views take forever In-reply-to: Your message of Thu, 11 Nov 1999 15:10:09 -0800 <382B4CD1.E7EB3C79@zotconsulting.com> Date: Thu, 11 Nov 1999 19:34:06 -0500 Message-ID: <21836.942366846@sss.pgh.pa.us> From: Tom Lane "Zot O'Connor" 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