It's (no surprise) about the data interacting with the PgSQL caching behavior (which I'd love Tom to pipe up about) The data include some very large polygons, that have a lot of overlap with other polygons. So they cause a lot of runs through the consistent check, which don't seem to happen in the immutable function case.
suelos=# select p1.gid, sum(npoints(p1.geom))/count(*) as npoints, count(*) from suelos1 p1, suelos1 p2 where p1.geom && p2.geom group by p1.gid order by count desc; gid | npoints | count ------+---------+------- 265 | 4889 | 236 136 | 2292 | 216 645 | 1215 | 132 315 | 842 | 116 237 | 1742 | 115 75 | 1339 | 97 589 | 1331 | 92 134 | 713 | 79 274 | 1316 | 73 807 | 1397 | 66 511 | 714 | 62 534 | 492 | 55 999 | 3693 | 55 1028 | 850 | 54 1067 | 1195 | 52 162 | 329 | 52 764 | 286 | 50 720 | 574 | 48 904 | 316 | 48 1313 | 1616 | 46 22 | 336 | 43 The amount re-calculation this kind of overlap forces can also be seen in the performance difference between the (preparedgeometry) st_intersects() and the (oldskool) st_dwithin() tests: suelos=# select count(*) from suelos1 p1, suelos1 p2 where st_dwithin(p1.geom, p2.geom, 0.0); count ------- 8566 (1 row) Time: 103100.780 ms suelos=# select count(*) from suelos1 p1, suelos1 p2 where st_intersects(p1.geom, p2.geom); count ------- 8566 (1 row) Time: 6136.734 ms Anyhow, the question remains, at a PgSQL level, why adding that immutable function causes so many fewer calls into GiST consistent (which makes so much difference). On Fri, Aug 14, 2009 at 10:34 AM, Paul Ramsey<[email protected]> wrote: > No surprise, any function that returns cheaply and immutably will > provide the same effect. The effect is so huge, that you can even get > faster results using expensive functions. > > suelos=# select count(*) from suelos1 p1, suelos1 p2 where > (st_envelope(p1.geom) && p2.geom); > count > ------- > 13808 > (1 row) > Time: 92.989 ms > > suelos=# select count(*) from suelos1 p1, suelos1 p2 where > (st_setsrid(p1.geom,23030) && p2.geom); > count > ------- > 13808 > (1 row) > Time: 92.626 ms > > suelos=# select count(*) from suelos1 p1, suelos1 p2 where > (st_convexhull(p1.geom) && p2.geom); count > ------- > 13808 > (1 row) > Time: 237.928 ms > > suelos=# select count(*) from suelos1 p1, suelos1 p2 where (p1.geom && > p2.geom); > count > ------- > 13808 > (1 row) > Time: 478.765 ms > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
