Re: Query optimizer 8.0.1 (and 8.0)

pgsql(at)mohawksoft(dot)com wrote:
>
> In this case, the behavior observed could be changed by altering the
> sample size for a table. I submit that an arbitrary fixed sample size is
> not a good base for the analyzer, but that the sample size should be based
> on the size of the table or some calculation of its deviation.
>

Mark,

Do you have any evidence that the Sample Size had anything to do
with the performance problem you're seeing?

I also do a lot with the complete Census/TIGER database.

Every problem I have with the optimizer comes down to the
fact that the data is loaded (and ordered on disk) by
State/County FIPS codes, and then queried by zip-code
or by city name. Like this:

Alabama 36101 [hundreds of pages with zip's in 36***]
Alaska 99686 [hundreds of pages with zip's in 9****]
Arizona 85701 [hundreds of pages with zip's in 855**]

Note that the zip codes are *NOT* sequential.

The "correlation" statistic sees that the Zip codes are not
sequential; so it makes the *HORRIBLE* assumption that they
are scattered randomly across the disk.

In reality, even though there's no total ordering of the
zip codes; any given zip code only exists on a couple
disk pages; so index scans would be the right choice.

But the single correlation parameter is not sufficient
to let the optimizer known this.

No matter how large a sample size you choose, ANALYZE
will correctly see that Zip codes and State FIPS codes
are non-correlated, and the optimizer will overestimate
the # of pages an index scan will need.

Ron

PS: I pointed out workarounds in my earlier posting
in this thread. Yes, I'm using the same TIGER data
you are.