[postgis-devel] test output

[Mark Cave-Ayland]

Hi strk,

Great work! I've just downloaded the latest CVS and tried the same
queries that weren't working when you first started writing your
test_estimation.pl. Here are the results with the statistics target set
to the default of 10:


pgtest=# explain analyze select * from wgs84_polygon where geom &&
setSRID('BOX3D(-116.203 36.042, -98.302 46.646)'::box3d, 4318);
NOTICE:  postgis_gist_sel called
NOTICE:   search_box overlaps 80.000000 cells
NOTICE:   avg feat overlaps 1.176333 cells
NOTICE:   SUM(ov_histo_cells)=0.067781
NOTICE:   gain=0.850099
NOTICE:   selectivity=0.057620
NOTICE:   returning computed value: 0.057620
                                                      QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------
 Seq Scan on wgs84_polygon  (cost=0.00..6694.85 rows=3024 width=2927)
(actual time=28.800..3033.904 rows=3250 loops=1)
   Filter: (geom && 'SRID=4318;BOX3D(-116.203 36.042 0,-98.302 46.646
0)'::geometry)
 Total runtime: 3037.157 ms
(3 rows)


pgtest=# explain analyze select * from wgs84_polygon where geom &&
setSRID('BOX3D(-115.439 41.362, -107.820 45.862)'::box3d, 4318);
NOTICE:  postgis_gist_sel called
NOTICE:   search_box overlaps 16.000000 cells
NOTICE:   avg feat overlaps 1.176333 cells
NOTICE:   SUM(ov_histo_cells)=0.009273
NOTICE:   gain=0.850099
NOTICE:   selectivity=0.007883
NOTICE:   returning computed value: 0.007883
                                                                QUERY
PLAN                                                    
------------------------------------------------------------------------
-------------------------------------------------------------------
 Index Scan using wgs84_polygon_idx on wgs84_polygon
(cost=0.00..1611.71 rows=414 width=2927) (actual time=0.268..7.065
rows=612 loops=1)
   Index Cond: (geom && 'SRID=4318;BOX3D(-115.439 41.362 0,-107.82
45.862 0)'::geometry)
 Total runtime: 7.567 ms
(3 rows)


pgtest=# explain analyze select * from wgs84_polygon where geom &&
setSRID('BOX3D(-117.964 44.097, -112.412 45.609)'::box3d, 4318);
NOTICE:  postgis_gist_sel called
NOTICE:   search_box overlaps 6.000000 cells
NOTICE:   avg feat overlaps 1.176333 cells
NOTICE:   SUM(ov_histo_cells)=0.002810
NOTICE:   gain=0.850099
NOTICE:   selectivity=0.002389
NOTICE:   returning computed value: 0.002389
                                                                QUERY
PLAN                                                    
------------------------------------------------------------------------
------------------------------------------------------------------
 Index Scan using wgs84_polygon_idx on wgs84_polygon  (cost=0.00..503.50
rows=126 width=2927) (actual time=0.121..3.032 rows=168 loops=1)
   Index Cond: (geom && 'SRID=4318;BOX3D(-117.964 44.097 0,-112.412
45.609 0)'::geometry)
 Total runtime: 3.233 ms
(3 rows)



pgtest=# explain analyze select * from wgs84_polygon where geom &&
setSRID('BOX3D(-179.0 -90.0, 179.0 90.0)'::box3d, 4318);
NOTICE:  postgis_gist_sel called
NOTICE:   search_box overlaps 89 columns on the bottom of histogram grid
NOTICE:   search_box overlaps 1600.000000 cells
NOTICE:   avg feat overlaps 1.176333 cells
NOTICE:   SUM(ov_histo_cells)=1.176333
NOTICE:   gain=0.850099
NOTICE:   selectivity=1.000000
NOTICE:   returning computed value: 1.000000
                                                       QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------
 Seq Scan on wgs84_polygon  (cost=0.00..6694.85 rows=52468 width=2927)
(actual time=0.262..2952.709 rows=52464 loops=1)
   Filter: (geom && 'SRID=4318;BOX3D(-179 -90 0,179 90 0)'::geometry)
 Total runtime: 2998.590 ms
(3 rows)


Glad that you found the dataset useful for testing purposes. The
estimates look fairly accurate, more than good enough for the PostgreSQL
estimator. Look forward to seeing how the algorithm will cope with
larger/different datasets that people will throw at it in the future :)


Thanks for all your hard work on this,

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.