[postgis-users] GiST index on multiple columns?

[Mark Cave-Ayland]

Hi Robin,

What you need is the contrib/btree_gist package which implements B-Tree
indices using GiST. It is then possible to create a multi-column GiST index
across the geometry column and your integer column (note that I haven't
tried this myself, so it would be interesting to know if this does actually
increase the efficiency of your queries).


Kind regards,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT 

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk
 

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Robin Chauhan
> Sent: 08 January 2005 06:30
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] GiST index on multiple columns?
> 
> 
> Hello,
> 
> I am looking for advice on using a GiST index on multiple columns.
> 
> Would a 2 column GiST index be effective to index 2 'point' 
> geometry columns, for queries in which each point is 
> constrained a distinct box?  I am able to create a GiST index 
> on 2 geometry columns, but I'm not sure that it's effective.  
> I didnt see mention of multiple column GiST indices in the 
> manual.  The 2 points will represent start and end points of 
> a journey.
> 
> Also, I want to improve the performance of a query which 
> involves conditions on both 2 spatial columns (of type 
> 'geometry') and 3
> non-spatial columns (of type integer and float).    Now GiST indexes
> appear to only allow geometric columns to be indexed.  So I 
> am considering storing/coercing numeric types into 
> geometries, just to allow them to be indexed along with the 
> geometries, hopefully improving performance.  The integer 
> types have a relatively small number of distinct values (like 20).
> 
> Could you comment on this scheme?  Could this really work?  
> If not, is there another way I can index more columns when 
> some are spatial (an R-Tree index maybe? They also do not 
> allow for simple non-spatial types...).
> 
> Thanks in advance for your advice,
> 
> -Robin Chauhan
> http://pirg.uwaterloo.ca/~robin/ 
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>