Re: raising the default default_statistics_target

"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:
> Hi Tom. I ran some very simple tests on analyze times and query plan
> times on a very simple table, with data randomly distributed. The index
> was on a date field, since that's what I was testing last.

Thanks.

> I also ran some quick tests on smaller tables (1000 and 10k rows) and
> there, the plateau that we see in the 100k analyze shows up much quicker,
> at something like 50 or so. I.e. the analyze time flattened out quickly
> and higher numbers cost very little if anything.

The sample size is (IIRC) 300 times stats_target rows, so the "plateau"
that you're seeing occurs when the sample size becomes the entire table.
It would be useful to note how large the ANALYZE process got to be during
these runs.

> Since this query was quite an easy plan, I'd expect to need a much more
> complex one to test the increase in planning time, say something that has
> to look at a lot of statistics. Any particular join type or something
> that's likely to do that?

I'd say try a join on any reasonably plausible foreign-key relationship
(unique key on one side, not-unique data on the other). That's probably
the most common situation. As for making it complicated, just stack up
a bunch of such joins ...

regards, tom lane