From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: queries with DISTINCT / GROUP BY giving different plans
Date: Tue, 20 Aug 2013 19:32:09 -0400
Message-ID: <20391.1377041529@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Tomas Vondra <tv@fuzzy.cz> 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:
inline patch 1: src/backend/optimizer/plan/planner.c (+4/-0 lines) download inline-1.patchdiff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, tv@fuzzy.cz
Subject: Re: queries with DISTINCT / GROUP BY giving different plans
In-Reply-To: <20391.1377041529@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox