> From: Tom Lane [mailto:[EMAIL PROTECTED] > I was intending to do it the other way, actually. An extra field in > HashPath hardly costs anything. The other reason for it is that there > are other possible uses for knowing whether a hash will be multi-batch. > (For example, if we were prepared to tell the executor that it *must* > keep the hash to one batch, we could assume that the sort order of the > left input is preserved. I haven't looked into the risks/benefits of > that too much, but it's been in the back of the mind for a long time.)
Having the number of batches in HashPath could be potentially useful for a variety of reasons. For our research, we have added an nbatch variable in both HashPath and HashJoin. Having it in HashJoin is useful as we modified EXPLAIN to output the number of batches. There are costs in putting an nbatch variable in HashPath as the system may set this variable potentially hundreds/thousands of times during costing and does not (currently) use it until you convert the chosen HashPath to a plan. > I'd be more inclined to deal with the issue by trying to establish a > "safety margin" in the estimate of whether the hash will go multi-batch. > IOW we should disuse_physical_tlist if the hash is estimated to be close > to but still within one batch. Our experiments with large TPC-H 1GB joins show that it is almost always better to not use physical_tlists if the number of batches is > 1. There is a noticeable (approximately 5-15%) improvement when using physical_tlists for in-memory joins. For batches of size 2, it sometimes can go either way depending how many attributes are projected out of the outer relation. Using physical_tlists may be better even for batches of size 2 if most of the attributes of the outer relation are kept. For a larger number of batches, the extra I/O cost significantly dominates over the physical_tlist optimization. Performance of multi-batch joins may improve 50% or more by disabling the optimization. It is possible to create a "safety margin" by having ExecChooseHashTableSize() return the value inner_rel_bytes/hash_table_bytes which represents the fraction of the memory available that the inner relation is expected to consume. You can then make decisions based on that. However, this is only as good as the inner relation size estimate and especially for large queries, the estimate may be quite inaccurate. A more robust solution could examine the "width" of the path and the "width" of the relation combined with the number of batches to see if projecting early would be worth it. It may be best to keep it simple and just use number of batches > 1 as a criteria and instead focus on examining issues with inaccurate join size estimates. -- Dr. Ramon Lawrence Assistant Professor, Department of Computer Science, University of British Columbia Okanagan E-mail: [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
