[postgis-users] question on gist performance

[Stefan Zweig]

Mark,

> Yes. Number 1 is true because we have a RECHECK clause on the operator 
> class - it is this that provides the SRID checking since the SRID is 
> stored in the heap and not in the index.
> This might make sense since if you're storing country outlines then 
> the the geometries are likely to be quite large...

Right, RECHECK && seems to be enabled by default for gist indices. Why is that needed and how can I disable the RECHECK clause? In my case, SRID lookups are not necessary when a CHECK (srid(the_geom)=4326) constraint is used since all imported objects are guaranteed to have 4326 then, or am I wrong? I wonder what consequences would have changes or insertions of Geometry objects if I do disable the RECHECK clause? Aren't such changes stored into the gist index back again automatically?

I further still do not understand why the RECHECK times for the the_geom and bbox columns differ: It seems that the cached Geometry bounding box is *not* used and the whole Geometry object is scanned, *even if* hasbbox(the_geom) returns true for all objects. In my eyes this decreases strongly and unnecessarily the performance of certain bounding box queries.

Thank you!


=====

Kevin,

I followed your short instruction and got about the same measurement times. No other processes were running on the server at that time.

Any further ideas?

Thank you!

> -----Ursprüngliche Nachricht-----
> Von: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Gesendet: 07.09.07 20:58:17
> An: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Betreff: RE: [postgis-users] question on gist performance


> 
> On Fri, 2007-09-07 at 14:17 +0200, Stefan Zweig wrote:
> > Gregory (and others),
> > 
> > some questions occurred after reading your posts: 
> > 
> > 1. Is it true that each index match is revalidated by looking at the corresponding data row?
> > 2. Even it is true, both queries (based on the_geom or bbox, respecively) should then perform the *same* revalidation on the *same* (TOASTed) row data, resulting in identical query times?
> > 3. Could anybody clarify where and when index and row data is stored in the file system? How do I know what files contains what indices and row data?
> > 4. The bounding box cache should be inherent in the index, so hasBBOX(the_geom) should not play any role? (Btw, the result of hasBBOX(the_geom) is always true in our case, I even tried SELECT addbbox(the_geom) from mytable - without effect).
> > 
> > Thank you.
> 
> Yes. Number 1 is true because we have a RECHECK clause on the operator
> class - it is this that provides the SRID checking since the SRID is
> stored in the heap and not in the index. This might make sense since if
> you're storing country outlines then the the geometries are likely to be
> quite large...
> 
> 
> ATB,
> 
> Mark.
> 
> -- 
> ILande - Open Source Consultancy
> http://www.ilande.co.uk
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 


_____________________________________________________________________
Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
http://smartsurfer.web.de/?mc=100071&distributionid=000000000066