You know, I don't think a lot of people "get" the issues I was describing, or maybe they don't believe it, I don't know, but, I think that it would be a useful contrib project to create an 'analyze_special('table', 'column', 'method')' function that does a better job at calculating the stats for table that contain multiple trend waveforms. A separate function will probably work well as the trends within the data probably only apply to specific rows.
I've done something similar, but simpler for the Census/TIGER data.
If you loaded each TIGER file sequentially, like I did, the data was all grouped by county when it was loaded - so basically all the geographical columns (zip, county, state, census-tract) are actually grouped tightly on disk -- though ANALYZE can't see this because they're not strictly ascending or descending.
Since I merely observed the geospatial columns were all clustered pretty well, I merely set the correlation value to the same pretty large value for all the geometric rows with a bunch of statements like this:
update pg_statistic
set stanumbers3[1] = 0.8
where starelid = 31412043
and staattnum=3;Instead of a complicated analyze function, how about just letting the user "tell" the optimizer that a column is clustered well with a function like:
force_correlation_stat('schema', 'table', 'column', 'value')would actually work well for your data. Since you
know your distinct values lay on a relatively small
number of pages if you merely did:
force_correlation('tiger','rt1','zipl',0.8);
force_correlation('tiger','rt1','statel',0.8);
force_correlation('tiger','rt1','countyl',0.8);
the optimizer would then see that not many disk
pages would need to be hit for a single zip code.It's interesting, because I don't think it needs to calculate a perfect representation of the data so much as better clue to its nature for the optimizer.
Indeed. Using the very arbitrary number "0.8" for the correlation, for all the geographic-related columns in the tiger data, the optimizer guessed a good plan almost every time on my company's 200GB geographical database.
When I get the time (or can get someone to pay me to do it) I'm going to try it.
I still suspect that the correct way to do it would not be to use the single "correlation", but 2 stats - one for estimating how sequential/random accesses would be; and one for estimating the number of pages that would be hit. I think the existing correlation does well for the first estimate; but for many data sets, poorly for the second type.
If you want to start a contrib project that looks into additional stats that may help, I might be interested.
Ron
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
