[postgis-users] GIST index speed

[Mark Cave-Ayland]

Mark Cave-Ayland wrote:

> *blinks* this is definitely a bug somewhere - there is no way that 
> adding a function wrapper to a constant should make the query several 
> orders of magnitude quicker :(

Hmmm. It seems the problem is related to whether or not we copy the 
incoming geometry. I've just created a really simple dummy function that 
  copies the input geometry to a new memory location that looks like this:


Datum LWGEOM_mcatest(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(LWGEOM_mcatest);
Datum LWGEOM_mcatest(PG_FUNCTION_ARGS)
{
         PG_LWGEOM *pgl = (PG_LWGEOM *)
		PG_DETOAST_DATUM(PG_GETARG_DATUM(0));
         void *mem;

         /* Copy somewhere else */
         mem = palloc(VARSIZE(pgl));
         memcpy(mem, pgl, VARSIZE(pgl)-VARHDRSZ);

         PG_RETURN_POINTER(mem);
}


CREATE OR REPLACE FUNCTION mcatest(geometry)
         RETURNS geometry
         AS '$libdir/lwpostgis','LWGEOM_mcatest'
         LANGUAGE 'C' IMMUTABLE STRICT;


If I then do:

postgis=# explain analyze select count(*) from geography where centroid 
&& (select mcatest(the_geom) from geography where id=69495);
 
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=7157.29..7157.30 rows=1 width=0) (actual 
time=664.406..664.408 rows=1 loops=1)
    InitPlan
      ->  Seq Scan on geography  (cost=0.00..7149.00 rows=1 width=4559) 
(actual time=181.347..182.944 rows=1 loops=1)
            Filter: (id = 69495::numeric)
    ->  Index Scan using geography_geom_centroid_idx on geography 
(cost=0.00..8.28 rows=1 width=0) (actual time=183.543..580.966 
rows=32880 loops=1)
          Index Cond: (centroid && $0)
          Filter: (centroid && $0)
  Total runtime: 666.027 ms
(8 rows)


...then I end up with the shorter time query plan??! I think I will punt 
this one over to pgsql-hackers to see if they can help work out where 
the problem lies.


ATB,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063