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
Attachment:
smime.p7s
Description: Signature cryptographique S/MIME
