Re: Bad n_distinct estimation; hacks suggested? - Mailing list pgsql-performance

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

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco