X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DD7893A40C4 for ; Mon, 8 Nov 2004 23:58:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86197-06 for ; Mon, 8 Nov 2004 23:57:59 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1212A3A408C for ; Mon, 8 Nov 2004 23:58:01 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iA8NvwjS012048; Mon, 8 Nov 2004 18:57:58 -0500 (EST) To: Carlos Lopez Cc: Simon Riggs , pgsql-performance@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [PERFORM] poor performance in migrated database In-reply-to: <20041108212841.75273.qmail@web52705.mail.yahoo.com> References: <20041108212841.75273.qmail@web52705.mail.yahoo.com> Comments: In-reply-to Carlos Lopez message dated "Mon, 08 Nov 2004 13:28:41 -0800" Date: Mon, 08 Nov 2004 18:57:58 -0500 Message-ID: <12047.1099958278@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/71 X-Sequence-Number: 15456 Carlos Lopez writes: > This is one of the queries that work,and is the first > in a 4 level nested query.... Do you really need UNION (as opposed to UNION ALL) in this query? The EXPLAIN shows that almost half the runtime is going into the sort/uniq to eliminate duplicates ... and according to the row counts, there are no duplicates, so it's wasted effort. I looked at your schema and saw an awful lot of SELECT DISTINCTs that looked like they might not be necessary, too. But I'm not willing to crawl through 144 views with no information about which ones are causing you problems. What's a typical query that you are unsatisfied with the performance of? regards, tom lane