Re: Bad n_distinct estimation; hacks suggested?
От | Marko Ristola |
---|---|
Тема | Re: Bad n_distinct estimation; hacks suggested? |
Дата | |
Msg-id | 426944D0.6040706@kolumbus.fi обсуждение исходный текст |
Ответ на | Re: Bad n_distinct estimation; hacks suggested? (Josh Berkus <josh@agliodbs.com>) |
Ответы |
Re: Bad n_distinct estimation; hacks suggested?
|
Список | pgsql-performance |
Hi. Sometimes, if the random number generator, that PostgreSQL uses, isn't good enough, the randomly selected pages for the statistics might not be random enough. Solaris is unknown to me. Maybe the used random number generator there isn't good enough? Good statistics depend on good random numbers. So, for example, if you have one million pages, but the upper bound for the random numbers is one hundred thousand pages, the statistics might get tuned. Or some random number generator has for example only 32000 different values. Regards, Marko Ristola Josh Berkus wrote: >Tom, > >Any thoughts? This is really messing up query execution all across the >database ... > >--Josh > > > >>Here is the stats = 100 version. Notice that n_distinct has gone down. >> >> schemaname | tablename | attname | null_frac | avg_width | >>n_distinct | most_common_vals >> >>| most_common_freqs >>| histogram_bounds | >> >>correlation >> >> > > > >>-------------------+------------- public | web_site_activity_fa | >>session_id | 0 | 8 | 96107 | >>{4393922,6049228,6026260,4394034,60341,4393810,2562999,2573850,3006299,4705 >>488,2561499,4705258,3007378,4705490,60327,60352,2560950,2567640,2569852,3006 >>604,4394329,2570739,2406633,2407292,3006356,4393603,4394121,6449083,2565815, >>4387881,2406770,2407081,2564340,3007328,2406578,2407295,2562813,2567603,4387 >>835,71014,2566253,2566900,6103079,2289424,2407597,2567627,2568333,3457448,23 >>450,23670,60743,70739,2406818,2406852,2407511,2562816,3007446,6306095,60506, >>71902,591543,1169136,1447077,2285047,2406830,2573964,6222758,61393,70955,709 >>86,71207,71530,262368,2289213,2406899,2567361,2775952,3006824,4387864,623982 >>5,6244853,6422152,1739,58600,179293,278473,488407,1896390,2286976,2407020,25 >>46720,2677019,2984333,3006133,3007497,3310286,3631413,3801909,4366116,438802 >>5} >> >>{0.00166667,0.00146667,0.0013,0.0011,0.000933333,0.0009,0.0008,0.0008,0.000 >>733333,0.000733333,0.0007,0.000633333,0.0006,0.0006,0.000566667,0.000566667, >>0.000566667,0.000566667,0.000566667,0.000566667,0.000566667,0.000533333,0.00 >>05,0.0005,0.0005,0.0005,0.0005,0.0005,0.000466667,0.000466667,0.000433333,0. >>000433333,0.000433333,0.000433333,0.0004,0.0004,0.0004,0.0004,0.0004,0.00036 >>6667,0.000366667,0.000366667,0.000366667,0.000333333,0.000333333,0.000333333 >>,0.000333333,0.000333333,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0. >>0003,0.0003,0.0003,0.000266667,0.000266667,0.000266667,0.000266667,0.0002666 >>67,0.000266667,0.000266667,0.000266667,0.000266667,0.000233333,0.000233333,0 >>.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000 >>233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.0002333 >>33,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0 >>002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002} >> >>{230,58907,88648,156764,216759,240405,264601,289047,312630,339947,364452,38 >>6486,409427,434075,455140,475759,500086,521530,544703,680376,981066,1313419, >>1712592,1860151,1882452,1905328,1927504,1948159,1970054,1990408,2014501,2038 >>573,2062786,2087163,2110129,2132196,2155657,2181058,2204976,2228575,2256229, >>2283897,2352453,2407153,2457716,2542081,2572119,2624133,2699592,2771254,2832 >>224,2908151,2951500,3005088,3032889,3137244,3158685,3179395,3203681,3261587, >>3304359,3325577,3566688,3621357,3645094,3718667,3740821,3762386,3783169,3804 >>593,3826503,3904589,3931012,3957675,4141934,4265118,4288568,4316898,4365625, >>4473965,4535752,4559700,4691802,4749478,5977208,6000272,6021416,6045939,6078 >>912,6111900,6145155,6176422,6206627,6238291,6271270,6303067,6334117,6365200, >>6395250,6424719,6888329} >> >>| 0.41744 >> >> > > >
В списке pgsql-performance по дате отправления: