> Hi all, > > I have some data [...] Thanks for gathering this data.
The first thing I notice is that the two versions of Q17 that you are running are actually not the exact same query - there are hard-coded constants that are different in each case, and that matters. The substituted parameter doesn't affect the plan, but the constants that are actually in the query do. It seems that some of the other queries may be similar - for example, Q2 has a couple of very fast runs (with default_stats_target 10 and 600) that probably got a better plan than the other runs, and Q6, Q9, and Q20 look suspiciously like there may have been two different plans in the mix as well, presumably because the actual queries varied somewhat. I think the only way to fix this is to run the test a large number of times with each DST and average. :-( Q17 looks like a pretty illustrative example of how a higher statistics target can (potentially) help: it enables the planner to realize that a qual on the part table is highly selective, and therefore switch to a nested loop w/index-scan instead of a hash join. (This may not be the only case, but I can't think of the others right now.) I haven't actually looked at the selectivity estimation code, but I'm assuming that if we have n MCVs then we can estimate that any non-MCV occurs with frequency < 1/n (in fact, < the frequency of the least-frequent MCV, but 1/n at most). So we want n to be large enough that 1/n is below the cutoff for switching to an index scan (otherwise, we'll end up using the hash join even when the qual selects an extremely infrequent value). It might be helpful to figure out where that cutoff is and what factors it depends on. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
