[postgis-users] multi-column index w/ btree & gist

[Paul Ramsey]

Two things you can do (a) enable the stats engine and have two separate 
indexes. With stats enabled, the planner will make better decisions 
about which index to use, and what times. (b) try a multikey index. You 
can do multikey GiST indexes, but all elements of the index must be 
GiST. That is where things get trickier. Most data types are bound to 
btree, not gist. So you have to have gist bindings for both your 
datatypes before you build the multikey index. There are some btree_gist 
bindings in the pgsql contrib directory. Probably suitable for testing. 
Your create index statement would end up looking something like:

create index blahidx on blah using gist (geom gist_geometry_ops,other 
gist_other_btree_ops);

So just like other multikey indexes, only using pure gist. The ultimate 
solution would be to write gist bindings for absolutely all the pgsql 
data types. Right now the contrib package only has a few.

I am not sure whether (a) or (b) will be more effective. We have had 
mixed results with multikey. Using stats has been effective as 
advertised, but can still be slow on big data sets, or with indexes that 
poorly segment the data.

P

Charlton Purvis wrote:
> Hi, folks:
> 
> I poked around on the discussion lists here as well as in pgsql land,
> and I don't have a clear picture on making multi-column indexes.
> 
> If my table looks like this:
> 
> sea_coos_model=# \d bari_um;
>                     Table "bari_um"
>    Column    |            Type             | Modifiers 
> -------------+-----------------------------+-----------
>  ubari       | double precision            | 
>  vbari       | double precision            | 
>  file_date   | date                        | 
>  time_index  | integer                     | 
>  time_stamp  | timestamp without time zone | 
>  the_geom    | geometry                    | 
>  speed       | double precision            | 
>  theta       | double precision            | 
>  label_theta | double precision            | 
>  label_speed | integer                     | 
>  label_char  | character(1)                | 
>  xi          | double precision            | 
>  yi          | double precision            |
> 
> I'd like to create an index something like this:
> 
> create index bari_um__multi on bari_um using btree(time_stamp),
> gist(the_geom);
> 
> No doubt that doesn't work.  But is there a way for me to do this?  Some
> multi-column indexing stuff appears here,
> http://www.sai.msu.su/~megera/postgres/gist/, but perhaps that is old?
> 
> Bottom line is that I want to tell the db to look for the time_stamp
> before making any georeferenced comparisons.
> 
> Thanks,
> 
> Charlton
> 
>  
>  
>  
> Charlton Purvis
> (803) 777-8858 : voice
> (803) 777-8833 : fax
> cpurvis at sc.edu
>  
> Advanced Solutions Group
> Department of Physics and Astronomy
> University of South Carolina
> Columbia, SC 29208
> 
> 
> _______________________________________________
> 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
      \_