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 92679536D2 for ; Mon, 4 Apr 2005 07:15:44 +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 94549-05 for ; Mon, 4 Apr 2005 06:15:39 +0000 (GMT) Received: from smtpout04-04.prod.mesa1.secureserver.net (smtpout04-04.prod.mesa1.secureserver.net [64.202.165.199]) by svr1.postgresql.org (Postfix) with SMTP id D698E536CE for ; Mon, 4 Apr 2005 07:15:37 +0100 (BST) Received: (qmail 28199 invoked from network); 4 Apr 2005 06:15:38 -0000 Received: from unknown (63.229.213.126) by smtpout04-04.prod.mesa1.secureserver.net (64.202.165.199) with ESMTP; 04 Apr 2005 06:15:38 -0000 Mime-Version: 1.0 (Apple Message framework v619.2) In-Reply-To: <17973.1112594057@sss.pgh.pa.us> References: <20050404050803.7175.qmail@webmail10.mesa1.secureserver.net> <17973.1112594057@sss.pgh.pa.us> Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: <3de3144f9eb6c83400f8d41584af47f2@indeq.com> Content-Transfer-Encoding: 7bit From: Mark Lubratt Subject: Re: Correcting Hash Join Estimates Date: Mon, 4 Apr 2005 01:15:38 -0500 To: pgsql-performance@postgresql.org X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/44 X-Sequence-Number: 11441 On Apr 4, 2005, at 12:54 AM, Tom Lane wrote: > 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? Yes. I forgot to show how the query is executed... select * from view_get_all_user_award2 where person_id = 1; > Do you really need the > DISTINCT constraint? Yes. > Do you really need the ORDER BY? The customer wants an initial ordering in the displayed data. > Can you > simplify the WHERE clause at all? > I originally had a bunch of LEFT JOINs. After reading Tow's "SQL Tuning", I was hoping to steer the planner into a more "optimal" plan by using a large where clause instead and doing the joins there (I think they're called implicit joins). I was able to shave a couple of hundred milliseconds off the execution time by doing this. > 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. > Unfortunately, I'm not sure I can restructure the data. I did consider materialized views. However, they couldn't be lazy and that seemed like a lot of extra work for the backend for very little improvement. If this sounds like decent performance to you... I guess I can just tell the complainers that it's as good as it's going to get (barring a major hardware upgrade...). Thanks! Mark