On Sat, Apr 25, 2009 at 6:42 AM, Grzegorz Jaskiewicz
<[email protected]> wrote:
> On 25 Apr 2009, at 04:52, Robert Haas wrote:
>> blow the hash-join plan out of the water anyway... but Stephen Frost
>> was telling me at JDcon East that he sometimes sets it to something
>> like 8GB when he's the only user on his apparently-quite-awesome
>> hardware...)
>
> For the record, because most queries have 5-6 joins here, I always set it up
> to 32MB on production. We don't have more than 100-150 connections, so it
> plays well on normal 32bit machine with 4GB.
>
> If what you wrote about hash-join is confirmed by others, than I am pretty
> much +100 for fixing it.
>
> (just my penny).
You may find the attached patch interesting to play around with. It
changes the NTUP_PER_BUCKET into a GUC called hash_load, and adds
EXPLAIN support to show the number of buckets and batches. This is
just for experimentation: I'm not in favor of adding Yet Another Thing
for users to tune, but if you try it out, you will see (I think) that
changing hash_load has a dramatic effect on the estimated cost of a
hash join but a much less dramatic effect on the actual run-time.
...Robert
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***************
*** 786,791 **** explain_outNode(StringInfo str,
--- 786,794 ----
quote_identifier(rte->eref->aliasname));
}
break;
+ case T_Hash:
+ appendStringInfo(str, " buckets=%d batches=%d",
+ ((Hash *) plan)->num_buckets, ((Hash *) plan)->num_batches);
default:
break;
}
*** a/src/backend/executor/nodeHash.c
--- b/src/backend/executor/nodeHash.c
***************
*** 48,53 **** static void ExecHashSkewTableInsert(HashJoinTable hashtable,
--- 48,55 ----
int bucketNumber);
static void ExecHashRemoveNextSkewBucket(HashJoinTable hashtable);
+ int hash_load;
+
/* ----------------------------------------------------------------
* ExecHash
***************
*** 391,399 **** ExecHashTableCreate(Hash *node, List *hashOperators)
* This is exported so that the planner's costsize.c can use it.
*/
- /* Target bucket loading (tuples per bucket) */
- #define NTUP_PER_BUCKET 10
-
void
ExecChooseHashTableSize(double ntuples, int tupwidth, bool useskew,
int *numbuckets,
--- 393,398 ----
***************
*** 463,469 **** ExecChooseHashTableSize(double ntuples, int tupwidth, bool useskew,
*num_skew_mcvs = 0;
/*
! * Set nbuckets to achieve an average bucket load of NTUP_PER_BUCKET when
* memory is filled. Set nbatch to the smallest power of 2 that appears
* sufficient.
*/
--- 462,468 ----
*num_skew_mcvs = 0;
/*
! * Set nbuckets to achieve an average bucket load of hash_load when
* memory is filled. Set nbatch to the smallest power of 2 that appears
* sufficient.
*/
***************
*** 474,480 **** ExecChooseHashTableSize(double ntuples, int tupwidth, bool useskew,
double dbatch;
int minbatch;
! lbuckets = (hash_table_bytes / tupsize) / NTUP_PER_BUCKET;
lbuckets = Min(lbuckets, INT_MAX / 2);
nbuckets = (int) lbuckets;
--- 473,479 ----
double dbatch;
int minbatch;
! lbuckets = (hash_table_bytes / tupsize) / hash_load;
lbuckets = Min(lbuckets, INT_MAX / 2);
nbuckets = (int) lbuckets;
***************
*** 490,496 **** ExecChooseHashTableSize(double ntuples, int tupwidth, bool useskew,
/* We expect the hashtable to fit in memory */
double dbuckets;
! dbuckets = ceil(ntuples / NTUP_PER_BUCKET);
dbuckets = Min(dbuckets, INT_MAX / 2);
nbuckets = (int) dbuckets;
--- 489,495 ----
/* We expect the hashtable to fit in memory */
double dbuckets;
! dbuckets = ceil(ntuples / hash_load);
dbuckets = Min(dbuckets, INT_MAX / 2);
nbuckets = (int) dbuckets;
*** a/src/backend/optimizer/path/costsize.c
--- b/src/backend/optimizer/path/costsize.c
***************
*** 1880,1887 **** cost_hashjoin(HashPath *path, PlannerInfo *root, SpecialJoinInfo *sjinfo)
&numbatches,
&num_skew_mcvs);
virtualbuckets = (double) numbuckets *(double) numbatches;
! /* mark the path with estimated # of batches */
path->num_batches = numbatches;
/*
* Determine bucketsize fraction for inner relation. We use the smallest
--- 1880,1888 ----
&numbatches,
&num_skew_mcvs);
virtualbuckets = (double) numbuckets *(double) numbatches;
! /* mark the path with estimated # of batches & buckets */
path->num_batches = numbatches;
+ path->num_buckets = numbuckets;
/*
* Determine bucketsize fraction for inner relation. We use the smallest
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 116,122 **** static Hash *make_hash(Plan *lefttree,
Oid skewTable,
AttrNumber skewColumn,
Oid skewColType,
! int32 skewColTypmod);
static MergeJoin *make_mergejoin(List *tlist,
List *joinclauses, List *otherclauses,
List *mergeclauses,
--- 116,124 ----
Oid skewTable,
AttrNumber skewColumn,
Oid skewColType,
! int32 skewColTypmod,
! int num_buckets,
! int num_batches);
static MergeJoin *make_mergejoin(List *tlist,
List *joinclauses, List *otherclauses,
List *mergeclauses,
***************
*** 1954,1960 **** create_hashjoin_plan(PlannerInfo *root,
skewTable,
skewColumn,
skewColType,
! skewColTypmod);
join_plan = make_hashjoin(tlist,
joinclauses,
otherclauses,
--- 1956,1964 ----
skewTable,
skewColumn,
skewColType,
! skewColTypmod,
! best_path->num_buckets,
! best_path->num_batches);
join_plan = make_hashjoin(tlist,
joinclauses,
otherclauses,
***************
*** 2766,2772 **** make_hash(Plan *lefttree,
Oid skewTable,
AttrNumber skewColumn,
Oid skewColType,
! int32 skewColTypmod)
{
Hash *node = makeNode(Hash);
Plan *plan = &node->plan;
--- 2770,2778 ----
Oid skewTable,
AttrNumber skewColumn,
Oid skewColType,
! int32 skewColTypmod,
! int num_buckets,
! int num_batches)
{
Hash *node = makeNode(Hash);
Plan *plan = &node->plan;
***************
*** 2787,2792 **** make_hash(Plan *lefttree,
--- 2793,2800 ----
node->skewColumn = skewColumn;
node->skewColType = skewColType;
node->skewColTypmod = skewColTypmod;
+ node->num_buckets = num_buckets;
+ node->num_batches = num_batches;
return node;
}
*** a/src/backend/optimizer/util/pathnode.c
--- b/src/backend/optimizer/util/pathnode.c
***************
*** 1493,1499 **** create_hashjoin_path(PlannerInfo *root,
*/
pathnode->jpath.path.pathkeys = NIL;
pathnode->path_hashclauses = hashclauses;
! /* cost_hashjoin will fill in pathnode->num_batches */
cost_hashjoin(pathnode, root, sjinfo);
--- 1493,1500 ----
*/
pathnode->jpath.path.pathkeys = NIL;
pathnode->path_hashclauses = hashclauses;
! /* cost_hashjoin will fill in pathnode->num_batches
! * and pathnode->num_buckets */
cost_hashjoin(pathnode, root, sjinfo);
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 36,41 ****
--- 36,42 ----
#include "commands/vacuum.h"
#include "commands/variable.h"
#include "commands/trigger.h"
+ #include "executor/nodeHash.h"
#include "funcapi.h"
#include "libpq/auth.h"
#include "libpq/pqformat.h"
***************
*** 1281,1286 **** static struct config_int ConfigureNamesInt[] =
--- 1282,1296 ----
8, 1, INT_MAX, NULL, NULL
},
{
+ {"hash_load", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Sets the target load factor for "
+ "hash tables constructed by a Hash executor node."),
+ NULL
+ },
+ &hash_load,
+ 10, 1, INT_MAX, NULL, NULL
+ },
+ {
{"geqo_threshold", PGC_USERSET, QUERY_TUNING_GEQO,
gettext_noop("Sets the threshold of FROM items beyond which GEQO is used."),
NULL
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 222,227 ****
--- 222,228 ----
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
+ #hash_load = 10 # target # of tuples per hash bucket
#------------------------------------------------------------------------------
*** a/src/include/executor/nodeHash.h
--- b/src/include/executor/nodeHash.h
***************
*** 47,50 **** extern void ExecChooseHashTableSize(double ntuples, int tupwidth, bool useskew,
--- 47,52 ----
int *num_skew_mcvs);
extern int ExecHashGetSkewBucket(HashJoinTable hashtable, uint32 hashvalue);
+ extern int hash_load;
+
#endif /* NODEHASH_H */
*** a/src/include/nodes/plannodes.h
--- b/src/include/nodes/plannodes.h
***************
*** 581,586 **** typedef struct Hash
--- 581,588 ----
AttrNumber skewColumn; /* outer join key's column #, or zero */
Oid skewColType; /* datatype of the outer key column */
int32 skewColTypmod; /* typmod of the outer key column */
+ int num_buckets; /* just for EXPLAIN */
+ int num_batches; /* just for EXPLAIN */
/* all other info is in the parent HashJoin node */
} Hash;
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
***************
*** 845,850 **** typedef struct HashPath
--- 845,851 ----
{
JoinPath jpath;
List *path_hashclauses; /* join clauses used for hashing */
+ int num_buckets; /* number of buckets expected */
int num_batches; /* number of batches expected */
} HashPath;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
