[postgis-devel] Re: geometry stats

[Mark Cave-Ayland]

Hi strk,

I've just managed to get my first successful query working using PG7.5
CVS and your new ANALYZE code for PostGIS :)


Along the way I found a bug in line 1187 of postgis_estimate.c:

overlapping_cells = (x_idx_max-x_idx_min+1) *
			(y_idx_max-x_idx_min+1);

				    ^^^

should actually read:

overlapping_cells = (x_idx_max-x_idx_min+1) *
			(y_idx_max-y_idx_min+1);


Using this (and removing the offending line from my previous post), I
imported a dataset of UK settlements in our database, created the
relevant indexes and ran an ANALYZE, followed by EXPLAIN ANALYZE:

 
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
---------
 Index Scan using us_gm_27700_point_gm_geom_idx on us_gm_27700_point
(cost=0.00..652.72 rows=192 width=319) (actual time=0.228..0.948
rows=105 loops=1)
   Index Cond: (gm_geom && 'SRID=-1;BOX3D(204029 39000 0,254000 68500
0)'::geometry)
 Total runtime: 1.529 ms
(3 rows)


Fantastic! The estimates on the spatial index don't look too bad at all,
good enough I would say to decide which is the correct index to use :)

Well done strk!


Cheers,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


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.