> -----Original Message----- > From: Jonah H. Harris [mailto:[EMAIL PROTECTED] > I have a new patch which does not create a bloom filter unless it sees > that the hash join is going to batch. I'll send it along later > tonight. > > Currently it's additional space not accounted for by work_mem. > Additionally, it's a good amount more space than is required. This is > fixed in the newer patch as well.
I think that the bloom filter will also improve the performance of in-memory joins as well. The basic trade-off in that case is the time to probe multiple entries in a bucket in the hash table (which currently defaults to 10) versus the cost of building/probing the bloom filter. The bloom filter should win in this case as long as there are tuples in the probe relation that cannot find a match in the build relation. My suggestion would be to keep it enabled for all joins. If possible, it would be valuable to try to estimate what percentage of tuples that the bloom filter filters out. A simple estimate would be to determine the percentage of the build table that is involved in the join. For instance, the good test cases had between 40-90% of the customer relation filtered out and a corresponding percentage of the probe relation, lineitem, was filtered out by the bloom filter. The bad case used all of customer, so the bloom filter stopped no probe tuples. It would be useful for testing to track the number and percentage of probe tuples that the bloom filter prevents a probe for. You may further record which of these tuples were in the in-memory batch and on-disk batches. These statistics may help you get the bloom filter optimized for all cases. -- Ramon Lawrence -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
