[postgis-users] composite key index (again)

[Paul Ramsey]

I don't know if there are GiST b-tree bindings for timestamp. Check 
contrib/btree-gist in the PostgreSQl source tree. If there are 
bindings, you can load the gist btree extensions, and then you should 
be able to build a multi-key gist index using both the geometries and 
the timestamps. No guarantees about actual index performance, but in 
theory it is "better" (now there is an untested assertion).

Paul

On Wednesday, August 11, 2004, at 08:23 PM, Charlton Purvis wrote:

> Hi, guys:
>
> I know I've bugged y'all about this before, but for the life of me I 
> can't remember what your 2 Canadian cents advice was.  And that's 
> worth a lot of USD!  Paul, I think you threw me a bone earlier, but I 
> can't find the thread neither physically nor mentally.
>
> Say I have this snazzy table:
>
> charlton (
>   my_time timestamp without time zone,
>   my_geom POINT
> );
>
> And say that every single MapServer query against this table is always 
> done by selecting both time and space.  I know I'll have to track down 
> the order, but say I also magically figure out that it's always time 
> and then space.  (Actually that's the way it should be -- I've beem 
> playing w/ the CLUSTER command, but unless I have the best indices 
> around, it won't be of much use).
>
> How can I create a composite key index w/ a timestamp without time 
> zone column and a geometry column?  Something akin to using a betree 
> for the time and a gist for the geom.  I know I can do a 
> btree(my_time, my_geom), but that won't cut it for the && operator I 
> don't think.  However, doing a gist of the two gives me a friendly:
>
> sea_coos_obs=# create index dang_index_doesnt_work on charlton using 
> gist(my_time,my_geom);
> ERROR:  data type timestamp without time zone has no default operator 
> class for access method "gist"
> HINT:  You must specify an operator class for the index or define a 
> default operator class
>
> I know the HINT actually means something to smart people.  Also, I 
> imagine I'd like to throw varchar in on the composite action.
>
> Mind jogging my memory?
>
> Thanks,
>
> Charlton
> <winmail.dat>_______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632