[postgis-devel] Re: [postgis-users] Possible index stats problem?

[strk at refractions.net]

On Tue, Sep 14, 2004 at 08:38:40AM +0200, strk at refractions.net wrote:
> [...]
> 
> > You got better results with lower statistics target.
> > Anyway, in both cases the estimated number or rows returned was
> > lower then the actual, so that is not the reason why the planner
> > choosed Sequencial scan.
> > The wrong estimation is due to a wrong estimate on the cost of
> > using the index as contrary to execute a sequencial scan.
> > 
> > As you can see the Sequencial scan has a cost 0.00..321.90
> > while the Index scan has an higher cost of 0.00..475.56.
> > This does not reflect reality as Sequencial scan took 48125+ms
> > while Index scan took 6718+ms, so cost estimation was not correct.
> 
> Mark, I think I've found the problem, but I'd like to hear your
> opinion as a pgsql hacker. A 'TODO'-labeled piece of code in
> postgis_estimate.c reminded me about this. The stawidth field 
> of the VacAttrStats structure to be filled by compute_geometry_stats()
> is currently filled by average width of sample geometries, but
> given cost estimations results it might need average width of index
> keys, does it sound correct ?
> 
> In the reported case postgresql is considering cost of index scan
> higher then it is, so I think this is the problem. 
> What do you think ?
> 
> --strk;

I get it back.
There is no change in cost estimation changing stawidth....
The cost estimation is performed by gistcostestimate() function,
which is defined by standard postgresql distribution and in turn
calls the default cost estimation routine.
Now I need to know how to use a custom one (or patch the gist one).
--strk;


> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel