> 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
ofthe 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