[postgis-users] GiST index on multiple columns?
m.cave-ayland at webbased.co.uk
Tue Jan 11 03:23:51 PST 2005
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).
South West Technology Centre
Tamar Science Park
T: +44 (0)1752 791021
F: +44 (0)1752 791023
> -----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?
> 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
> postgis-users mailing list postgis-users at postgis.refractions.net
More information about the postgis-users