X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 208EE536E9 for ; Mon, 4 Apr 2005 06:54:26 +0100 (BST) 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 88389-05 for ; Mon, 4 Apr 2005 05:54:21 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id CF85A536E2 for ; Mon, 4 Apr 2005 06:54:20 +0100 (BST) 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 j345sHk9017974; Mon, 4 Apr 2005 01:54:17 -0400 (EDT) To: mark.lubratt@indeq.com Cc: pgsql-performance@postgresql.org Subject: Re: Correcting Hash Join Estimates In-reply-to: <20050404050803.7175.qmail@webmail10.mesa1.secureserver.net> References: <20050404050803.7175.qmail@webmail10.mesa1.secureserver.net> Comments: In-reply-to mark.lubratt@indeq.com message dated "Sun, 03 Apr 2005 22:08:03 -0700" Date: Mon, 04 Apr 2005 01:54:17 -0400 Message-ID: <17973.1112594057@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/43 X-Sequence-Number: 11440 mark.lubratt@indeq.com writes: > I'm trying to optimize a query and the EXPLAIN ANALYZE (see link below) > shows that some hash join row estimates are wrong by a factor of 2-3, > and upwards of 7-8. I doubt that improving those estimates would lead to markedly better results. You need to think about improving the view design instead. What context is this view used in --- do you just do "select * from view_get_all_user_award2", or are there conditions added to it, or perhaps it gets joined with other things? Do you really need the DISTINCT constraint? Do you really need the ORDER BY? Can you simplify the WHERE clause at all? Half a second sounds pretty decent to me for a ten-way join with a WHERE clause as unstructured as that. If you really need it to execute in way less time, you're probably going to have to rethink your data representation to make the query simpler. regards, tom lane