[postgis-users] GIST index speed

[Paul Ramsey]

IMMUTABLE.

I bet when you have the function it's caching the result, and when you
don't, it's re-fetching it. And because the bare result is a toasted
tuple, the re-fetch is a lot more expensive.

P.

On Mon, Jun 9, 2008 at 5:39 AM, Mark Cave-Ayland
<mark.cave-ayland at siriusit.co.uk> wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>