Re: What happens between end of explain analyze and end of query execution ?

Le 06/04/2013 18:27, Tom Lane a écrit :
>> Ok, the problem definitely comes from the default_statistics_target
>> which is obviously too high on the database.
> Yeah, eqjoinsel() is O(N^2) in the lengths of the MCV lists, in the
> worst case where there's little overlap in the list memberships.
> The actual cost would depend a lot on the specific column datatypes.
>
> Not sure about your report that re-analyzing with the same stats target
> made a significant difference. It might have been a matter of chance
> variation in the sampled MCV list leading to more or fewer matches.
>
> regards, tom lane
>
>
Thank you all for your help, I appreciate it really much.

Just a last note: maybe the documentation could draw the attention on
this side effect of high statistics target.
Right now it says:
"Larger values increase the time needed to do ANALYZE, but might
improve the quality of the planner's estimates"
(http://www.postgresql.org/docs/9.2/static/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET)
and: "Raising the limit might allow more accurate planner estimates to
be made, particularly for columns with irregular data distributions, at
the price of consuming more space in pg_statistic and slightly more time
to compute the estimates."
(http://www.postgresql.org/docs/9.2/static/planner-stats.html)

It could be noted that a too high target can also have a noticeable cost
on query planning.

Best regards,
Franck