[postgis-devel] estimated_extent & pg_statistic

Mark Cave-Ayland m.cave-ayland at webbased.co.uk
Thu Feb 23 02:30:17 PST 2006


> From: postgis-devel-bounces at postgis.refractions.net
[mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Martin
Schäfer
> Sent: 23 February 2006 09:21
> To: PostGIS Development Discussion
> Subject: RE: [postgis-devel] estimated_extent & pg_statistic
>	
>	
> Hi strk,
>	 
>	The pg_statistic.stanumbers1 don't make it into the pg_stats view,
because stakind1
> has a value of 100, instead of 2.
>	 
>	I know nothing about the meaning of the pg_statistic columns or in
particular the 
> meaning of the values 2 and 100, but there seem to be two options:
>	 
>	1) Set stakind1 to 2 instead of 100. I don't know whether this
breaks anything.
>	2) Set stakind2 (currently 0) to 2 and set stanumbers2 (currently
null) to 
>	stanumbers1[5:8].
>	 
>	I presume all of these values are set during vacuum analyze.
>	 
>	If any of the two options were realized, then the estimated extent
will appear as 
> pg_stats.most_common_freqs and the estimated_extent() function could be
changed to use 
> pg_stats instead of pg_statistic.
>	 
>	Would it be possible to change the vacuum analyze code accordingly?
>	 
>	Martin


Hi Martin,

I would argue that we need to take a different approach to this problem,
since looking at the source of the pg_stats view, it makes very strong
assumptions that the pg_statistic table only holds statistics generated by
the standard PostgreSQL ANALYZE function rather than our own. For example,
the fields n_distinct, most_common_vals and most_common_freqs mean nothing
in the context of the PostGIS ANALYZE function.

The issue with the stakind field is that the PostGIS project has been
allocated a set of stakind values by the PostgreSQL core team, starting at
100. Any values below this are reserved and changing it might start breaking
parts of the optimiser, since we're telling it that our arrays contain data
in PostgreSQL ANALYZE format when in fact we are using our own.

As I mentioned before, I believe the best solution would be to alter the
definition of estimated_extent() in lwpostgis.sql so that it is marked as
SECURITY DEFINER, and so will run with the privileges of the database super
user rather than the current session user. Could you try this and confirm
that it works in your case?


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.





More information about the postgis-devel mailing list