Postgres Pro
Downloads
Home   >   mailing lists

Thread: multicolumn GIST index question

multicolumn GIST index question

From
Ron Mayer
Date:
Did anyone get multi-column GIST indexes working using both
the gist_btree and postgis modules?

It seems to work fine for me on small test cases (shown
at the bottom), but seems to crash my database for large
ones.   Any advice is welcome - including pointers to better
lists to ask questions like this.

Output showing a crash (on a large database) and a
successful run on a tiny one follow.

   Ron

fl=# -- sessionid is a text;  the_geom is a GEOMETRY
fl=# create index testidx2 on user_point_features using gist
(sessionid,the_geom);
server closed the connection unexpectedly
         This probably means the server terminated abnormally
         before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
fl=#
fl=#
fl=#
fl=# create table test3(a text, b text, c text);
CREATE TABLE
fl=# select addgeometrycolumn ('','test3','the_geom','-1','POINT',2);
                             addgeometrycolumn
--------------------------------------------------------------------------
  public.test3.the_geom SRID:-1 TYPE:POINT DIMS:2
  geometry_column fixed:0
(1 row)
fl=# create index idx_text_text on test3 using gist (a,b);
CREATE INDEX
fl=# create index idx_text_geom on test3 using gist (a,the_geom);
CREATE INDEX
fl=#

Re: multicolumn GIST index question

From
Sebastian Böck
Date:
Ron Mayer wrote:
>
> Did anyone get multi-column GIST indexes working using both
> the gist_btree and postgis modules?

Not quite stable yet.

> It seems to work fine for me on small test cases (shown
> at the bottom), but seems to crash my database for large
> ones.   Any advice is welcome - including pointers to better
> lists to ask questions like this.

For me it seems to work only if the geom-column
is the first column in a multicolumn-index.

Haven't investigated further so far...

HTH

Sebastian

Re: multicolumn GIST index question

From
Teodor Sigaev
Date:
>
> Did anyone get multi-column GIST indexes working using both
> the gist_btree and postgis modules?
It must.

> fl=# -- sessionid is a text;  the_geom is a GEOMETRY
> fl=# create index testidx2 on user_point_features using gist
> (sessionid,the_geom);
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Succeeded.
> fl=#
> fl=#
> fl=#
> fl=# create table test3(a text, b text, c text);
> CREATE TABLE
> fl=# select addgeometrycolumn ('','test3','the_geom','-1','POINT',2);
>                             addgeometrycolumn
> --------------------------------------------------------------------------
>  public.test3.the_geom SRID:-1 TYPE:POINT DIMS:2
>  geometry_column fixed:0
> (1 row)
> fl=# create index idx_text_text on test3 using gist (a,b);
> CREATE INDEX
> fl=# create index idx_text_geom on test3 using gist (a,the_geom);
> CREATE INDEX
> fl=#


Is a sessionid from user_point_features table unique?

Pls, try to compile database with --enable-cassert --enable-debug and send gdb
output.




--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/

Re: multicolumn GIST index question

From
Greg Stark
Date:
> Ron Mayer wrote:
> > Did anyone get multi-column GIST indexes working using both
> > the gist_btree and postgis modules?

Multi-column gist indexes are basically useless at this point. The index pages
are split based entirely on the first column, so the index becomes basically
an index on the first column.

There was some discussion on changing this but there wasn't consensus on which
direction to head with it. It may come up again for 8.1 if someone wants to
look at it.

--
greg