Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > So that's at least going in the right direction.
I agree that this is going in the right direction; it certainly would make the plan that I *expect* to be chosen more likely, however.. I've been fiddling with this on the very much larger overall database where this test case came from and have found that hashing the large table can actually be *faster* and appears to cause a more consistent and constant amount of disk i/o (which is good). The test case exhibits a bit of why this is the case- the per-tuple hash lookup is way closer to the per-tuple cost of building the hash table than I'd expect. per-tuple cost to build the hash table (41M tuples): 0.33us per-tuple cost to scan/do hash lookups (41M tuples): 0.29us (with a small hash table of only 41K entries) The total difference being: 1233.854 vs. 1428.424, or only 194.570ms in favor of scanning the big table instead of hashing it. These numbers are just from those posted with my original email: http://explain.depesz.com/s/FEq http://explain.depesz.com/s/FOU I've seen much worse though- I have one case where hash-the-big-table took 5s and hash-the-small-table took almost 10s (total times). I'm trying to see if I can pull that out and isolate how it's different (and see if it was just due to other load on the box). What I'm trying to get at in this overall email is: why in the world is it so expensive to do hash lookups? I would have expected the cost of the hash table to be *much* more than the cost to do a hash lookup, and that doing hash lookups against a small hash table would be fast enough to put serious pressure on the i/o subsystem. Instead, the building of the hash table actually puts more pressure and can end up being more efficient overall. We have a process that basically does this a whole bunch and the "hash-the-big-table" operation takes about 4.7 hrs, while the "hash-the-small-table" approach went well past 5 hours and was only about 70% complete. Thoughts? Thanks, Stephen
signature.asc
Description: Digital signature
