On Thu, Feb 16, 2017 at 2:38 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: >> On Thu, Feb 16, 2017 at 2:02 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> This might be overly aggressive, because it will pretty much shut off >>> any attempt to use hash joining on a large inner relation unless we >>> have statistics for it (and those stats are favorable). But having >>> seen this example, I think we need to be worried. > >> I do think that's worrying, but on the other hand it seems like this >> solution could disable many hash joins that would actually be fine. I >> don't think the largest ndistinct estimates we ever generate are very >> large, and therefore this seems highly prone to worry even when >> worrying isn't really justified. > > I initially thought about driving the shutoff strictly from the estimate > of the MCV frequency, without involving the more general ndistinct > computation that estimate_hash_bucketsize does. I'm not sure how much > that would do for your concern, but at least the MCV frequency doesn't > involve quite as much extrapolation as ndistinct.
Hmm, so we could do something like: if the estimated frequency of the least-common MCV is enough to make one bucket overflow work_mem, then don't use a hash join? That would still be prone to some error (in both directions, really) but it seems less likely to spit out completely stupid results than relying on ndistinct, which never gets very big even in a 10TB table. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
