[postgis-users] GiST index on multiple columns?

Mark Cave-Ayland m.cave-ayland at webbased.co.uk
Tue Jan 11 03:23:51 PST 2005

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,


WebBased Ltd
South West Technology Centre
Tamar Science Park
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

More information about the postgis-users mailing list