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

[Charlton Purvis]

Thanks, Paul:

I've hit a few deadlines, so I haven't had a chance to keep going down
this route, but I will eventually have to cross this barrier.

Will post results when I get there.

Thanks again,

Charlton

> -----Original Message-----
> From: Paul Ramsey [mailto:pramsey at refractions.net]
> Sent: Friday, September 26, 2003 10:47 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] multi-column index w/ btree & gist
> 
> 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
>       \_
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users