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 B537C539A6 for ; Mon, 4 Apr 2005 06:08:10 +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 76152-09 for ; Mon, 4 Apr 2005 05:08:05 +0000 (GMT) Received: from smtpout04-03.prod.mesa1.secureserver.net (smtpout04-03.prod.mesa1.secureserver.net [64.202.165.198]) by svr1.postgresql.org (Postfix) with SMTP id 02F0553980 for ; Mon, 4 Apr 2005 06:08:03 +0100 (BST) Received: (qmail 22239 invoked from network); 4 Apr 2005 05:08:03 -0000 Received: from unknown (HELO webmail10.mesa1.secureserver.net) (64.202.189.47) by smtpout04-03.prod.mesa1.secureserver.net with SMTP; 4 Apr 2005 05:08:03 -0000 Received: (qmail 7176 invoked by uid 99); 4 Apr 2005 05:08:03 -0000 Message-ID: <20050404050803.7175.qmail@webmail10.mesa1.secureserver.net> Date: Sun, 3 Apr 2005 22:08:03 -0700 From: mark.lubratt@indeq.com Subject: Correcting Hash Join Estimates To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: TEXT/plain; CHARSET=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.262 tagged_above=0 required=5 tests=AWL, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200504/42 X-Sequence-Number: 11439 Hello! I posted a similar question to this one about a month ago; but, for some reason, it never seemed to be broadcast eventhough it ended up in the archives. So, since I'm still struggling with this, I thought I'd repost... 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. There is a corresponding mis-estimation of the amount of time taken for these steps. The database is vacuum analyzed nightly by a cron job. How would I go about tightening up these errors? I suspect that I need to SET STATISTIC on some columns, but how can I tell which columns? Any help would be appreciated. WinXP (dual Xeon 1.2GB RAM) PgSQL 8.0.1 Explain Analyze: View Definition: The largest table contains about 10,000 rows. All tables have indexes on their foreign keys. Thanks! Mark