Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VBvPO-00020a-Ro for pgsql-performance@arkaria.postgresql.org; Tue, 20 Aug 2013 23:32:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VBvPO-0006PJ-7R for pgsql-performance@arkaria.postgresql.org; Tue, 20 Aug 2013 23:32:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VBvPN-0006PD-DO for pgsql-performance@postgresql.org; Tue, 20 Aug 2013 23:32:17 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VBvPJ-0001EK-VX for pgsql-performance@postgresql.org; Tue, 20 Aug 2013 23:32:17 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id r7KNW9sM020392; Tue, 20 Aug 2013 19:32:09 -0400 From: Tom Lane To: Tomas Vondra cc: pgsql-performance@postgresql.org Subject: Re: queries with DISTINCT / GROUP BY giving different plans In-reply-to: <5213EBE4.5030302@fuzzy.cz> References: <3a56c4d2c0b2daa11a625c2983823901.squirrel@sq.gransy.com> <11586.1376505314@sss.pgh.pa.us> <520E7F33.7010305@fuzzy.cz> <7059.1377015896@sss.pgh.pa.us> <5213ADB1.3040807@fuzzy.cz> <13875.1377032529@sss.pgh.pa.us> <5213EBE4.5030302@fuzzy.cz> Comments: In-reply-to Tomas Vondra message dated "Wed, 21 Aug 2013 00:21:24 +0200" Date: Tue, 20 Aug 2013 19:32:09 -0400 Message-ID: <20391.1377041529@sss.pgh.pa.us> X-Pg-Spam-Score: -4.7 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Tomas Vondra writes: > Not quite sure how to parse this (not a native speaker here, sorry). > Does that mean we want to keep it as it is now (because fixing it would > cause even worse errors with low estimates)? Or do we want to fix > hashed_distinct so that it behaves like hashed_grouping? We need to fix hashed_distinct like this: diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index bcc0d45..99284cb 100644 *** a/src/backend/optimizer/plan/planner.c --- b/src/backend/optimizer/plan/planner.c *************** choose_hashed_distinct(PlannerInfo *root *** 2848,2854 **** --- 2848,2858 ---- * Don't do it if it doesn't look like the hashtable will fit into * work_mem. */ + + /* Estimate per-hash-entry space at tuple width... */ hashentrysize = MAXALIGN(path_width) + MAXALIGN(sizeof(MinimalTupleData)); + /* plus the per-hash-entry overhead */ + hashentrysize += hash_agg_entry_size(0); if (hashentrysize * dNumDistinctRows > work_mem * 1024L) return false; I've started a thread over in -hackers about whether it's prudent to back-patch this change or not. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance