API for optimized predicates (was Re: [postgis-devel] 1.3.3)

[Paul Ramsey]

MEMCMP:

select e.id, count(*) from ed2000 e, vada2005 v where
st_contains(e.the_geom, v.centroid, NULL) group by e.id;

Time: 6518.143 ms

UNPREPARED:

select e.id, count(*) from ed2000 e, vada2005 v where
st_contains(e.the_geom, v.centroid) group by e.id;

Time: 34362.747 ms

IDCMP:

select e.id, count(*) from ed2000 e, vada2005 v where
st_contains(e.the_geom, v.centroid, e.gid) group by e.id;

Time: 6204.834 ms

DATA:

# select count(*) from vada2005;
 count
-------
  8792

# select count(*) from ed2000;
 count
-------
    79

# select 100*h, count(*) from (select
npoints(geometryn(the_geom,1))/100 as h from ed2000) as foo group by
h;
 ?column? | count
----------+-------
      100 |    25
      200 |    11
      300 |     6
      400 |     1
      500 |     2
      600 |     4
      700 |     1
      800 |     2
      900 |     1
     1200 |     2
     1300 |     1
     1400 |     2
     1500 |     2
     1600 |     1
     1700 |     1
     2200 |     2
     2300 |     2
     2500 |     1
     2700 |     1
     2800 |     2
     3100 |     1
     3600 |     1
     3700 |     1
     3900 |     1
     4200 |     1
     5000 |     1
     5600 |     1
     6400 |     1
    10600 |     1

All polygons have > 100 points, about 30% have more than 1000.