[postgis-devel] Strange result about spatial indexes?

[Paul Ramsey]

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<pramsey at opengeo.org> 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
>