Le Monday 10 March 2008, Peter Eisentraut a écrit : > Am Freitag, 7. März 2008 schrieb Tom Lane: > > I'm not wedded to the number 1000 in particular --- obviously that's > > just a round number. But it would be good to see some performance tests > > with larger settings before deciding that we don't need a limit. > > Well, I'm not saying we should raise the default statistics target. But > setting an arbitrary limit on the grounds that larger values might slow the > system is like limiting the size of tables because larger tables will cause > slower queries. Users should have the option of finding out the best > balance for themselves. If there are concerns with larger statistics > targets, we should document them. I find nothing about this in the > documentation at the moment.
I find 2 things: «Increasing the target causes a proportional increase in the time and space needed to do ANALYZE. » in http://www.postgresql.org/docs/current/static/sql-analyze.html and « ... at the price of consuming more space in pg_statistic and slightly more time to compute the estimates» in http://www.postgresql.org/docs/current/static/planner-stats.html But probably not clear enought about time impact in query plan. > > > IIRC, egjoinsel is one of the weak spots, so tests involving planning of > > joins between two tables with large MCV lists would be a good place to > > start. > > I have run tests with joining two and three tables with 10 million rows > each, and the planning times seem to be virtually unaffected by the > statistics target, for values between 10 and 800000. They all look more or > less like this: > > test=# explain select * from test1, test2 where test1.a = test2.b; > QUERY PLAN > --------------------------------------------------------------------------- >-- Hash Join (cost=308311.00..819748.00 rows=10000000 width=16) > Hash Cond: (test1.a = test2.b) > -> Seq Scan on test1 (cost=0.00..144248.00 rows=10000000 width=8) > -> Hash (cost=144248.00..144248.00 rows=10000000 width=8) > -> Seq Scan on test2 (cost=0.00..144248.00 rows=10000000 > width=8) (5 rows) > > Time: 132,350 ms > > and with indexes > > test=# explain select * from test1, test2 where test1.a = test2.b; > QUERY PLAN > --------------------------------------------------------------------------- >----------------- Merge Join (cost=210416.65..714072.26 rows=10000000 > width=16) > Merge Cond: (test1.a = test2.b) > -> Index Scan using test1_index1 on test1 (cost=0.00..282036.13 > rows=10000000 width=8) > -> Index Scan using test2_index1 on test2 (cost=0.00..282036.13 > rows=10000000 width=8) > (4 rows) > > Time: 168,455 ms > > The time to analyze is also quite constant, just before you run out of > memory. :) The MaxAllocSize is the limiting factor in all this. In my > example, statistics targets larger than about 800000 created pg_statistic > rows that would have been larger than 1GB, so they couldn't be stored. > > I suggest that we get rid of the limit of 1000, adequately document > whatever issues might exist with large values (possibly not many, see > above), and add an error message more user-friendly than "invalid memory > alloc request size" for the cases where the value is too large to be > storable. -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org
signature.asc
Description: This is a digitally signed message part.
