Re: GiST indexing problems... - Mailing list pgsql-hackers

David,

GiST prior 7.1 was broken in several respects. Please,
try 7.1 and examples from contrib/intarray. It should works.
btw, you'll have compress function actually works.
Regards,
    Oleg
On Sat, 5 May 2001, David McWherter wrote:

>
> Sure.  My postgresql version is 7.0.2.
>
> My database has a datatype called graph that looks like this:
>
> CREATE TYPE graph (
>         internallength = VARIABLE,
>         input = graph_in,
>         output = graph_out
> );
> CREATE OPERATOR ~ (
>         leftarg = graph,
>         rightarg = graph,
>         procedure = graph_distance,
>         commutator = ~
>         );
>
> And it has a datatype 'graphrange':
>
> CREATE FUNCTION graph_inrange(graph, graphrange)
>         RETURNS bool
>         AS '/usr/remote/home_u/udmcwher/myprojs/pg_graph.2/graph.so'
>         language 'c';
>
> CREATE TYPE graphrange (
>         internallength = VARIABLE,
>         input = graphrange_in,
>         output = graphrange_out
> );
> CREATE OPERATOR << (
>         leftarg = graph,
>         rightarg = graphrange,
>         procedure = graph_inrange
> );
>
> I have a bunch of GiST operators that are created like this:
>   CREATE FUNCTION gist_graph_consistent(opaque,graphrange)
>         RETURNS bool
>         AS '/usr/remote/home_u/udmcwher/myprojs/pg_graph.2/graph.so'
>         language 'c';
>   /* the same for gist_graph_{compress,decompress,penalty,picksplit,union,same} */
>
>
>
> I've tried adding the parameters 'restrict = eqsel' and 'join = eqjoinsel'
> to the datatype operators, but that doesn't seem to change anything.
>
>
> I construct a new opclass like this:
>
> INSERT INTO pg_opclass (opcname,opcdeftype)
>         values ( 'gist_graphrange_ops' );
>
> SELECT o.oid AS opoid, o.oprname
> INTO TABLE graph_ops_tmp
> FROM pg_operator o, pg_type t
> WHERE o.oprleft = t.oid
>    and t.typname = 'graph';
> INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
>         SELECT am.oid, opcl.oid, c.opoid, 1
>         FROM pg_am am, pg_opclass opcl, graph_ops_tmp c
>         WHERE amname = 'gist' and opcname = 'gist_graphrange_ops'
>                 and c.oprname = '<<';
>
>
>
> INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
>    SELECT am.oid, opcl.oid, pro.oid, 1
>    FROM pg_am am, pg_opclass opcl, pg_proc pro
>    WHERE  amname = 'gist' and opcname = 'gist_graphrange_ops'
>       and proname = 'gist_graph_consistent';
>
> INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
>    SELECT am.oid, opcl.oid, pro.oid, 2
>    FROM pg_am am, pg_opclass opcl, pg_proc pro
>    WHERE  amname = 'gist' and opcname = 'gist_graphrange_ops'
>       and proname = 'gist_graph_union';
>
> INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
>    SELECT am.oid, opcl.oid, pro.oid, 3
>    FROM pg_am am, pg_opclass opcl, pg_proc pro
>    WHERE  amname = 'gist' and opcname = 'gist_graphrange_ops'
>       and proname = 'gist_graph_compress';
>
> INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
>    SELECT am.oid, opcl.oid, pro.oid, 4
>    FROM pg_am am, pg_opclass opcl, pg_proc pro
>    WHERE  amname = 'gist' and opcname = 'gist_graphrange_ops'
>       and proname = 'gist_graph_decompress';
>
> INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
>    SELECT am.oid, opcl.oid, pro.oid, 5
>    FROM pg_am am, pg_opclass opcl, pg_proc pro
>    WHERE  amname = 'gist' and opcname = 'gist_graphrange_ops'
>       and proname = 'gist_graph_penalty';
>
> INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
>    SELECT am.oid, opcl.oid, pro.oid, 6
>    FROM pg_am am, pg_opclass opcl, pg_proc pro
>    WHERE  amname = 'gist' and opcname = 'gist_graphrange_ops'
>       and proname = 'gist_graph_picksplit';
>
> INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
>    SELECT am.oid, opcl.oid, pro.oid, 7
>    FROM pg_am am, pg_opclass opcl, pg_proc pro
>    WHERE  amname = 'gist' and opcname = 'gist_graphrange_ops'
>       and proname = 'gist_graphrange_same';
>
> I construct a table like this:
>
> CREATE TABLE repos ( a graph, file varchar(512) );
> INSERT INTO repos VALUES ( import_graphfile('/tmp/test1'), '/tmp/test1' );
> INSERT INTO repos VALUES ( import_graphfile('/tmp/test2'), '/tmp/test2' );
>
> What this does is a little bit weird, it reads in the test1 and test2 datafiles
> into the database, storing them as large objects.  Then, it constructs
> graph objects which have their oid's, and returns them from import_graphfile.
>
> I then try to construct an index like this:
>
> CREATE INDEX repos_index ON repos
>          USING gist ( a gist_graphrange_ops ) ;
>
> I've also tried a:graph and a:graphrange, but I don't think it changes anything.
>
> My queries look like:
>
>    SELECT * from repos where a << '(oid-num,int-num)'::graphrange;
>
> The function operator returns a boolean if a particular relation holds between
> the graph object and the graphrange object.
>
> The GiST compress operator will convert leaf GRAPH keys into
> graphrange keys for internal use.  Each of my GiST operators
> call elog( DEBUG, "function-name" ) as they're called.  When
> constructing the index, compress,decompress,picksplit,union
> are called as expected.  During the execution of the query,
> however, nothing happens.
>
> I've found the same exact results using the 'pggist' examples
> (a suite including intproc,boxproc,polyproc,textproc),
> and the examples found here: http://wit.mcs.anl.gov/~selkovjr/pg_extensions/contrib-7.0.tgz.
> The 'cube' test suite at that site is somewhat straightforward
> to invoke, and shows the same results.
>
> -david
>
>
> Oleg Bartunov writes:
>  > David,
>  >
>  > could you provide more info (scheme, query, postgresql version)
>  >
>  >     Regards,
>  >
>  >         Oleg
>  > On Sat, 5 May 2001, David McWherter wrote:
>  >
>  > >
>  > >      I've been doing some research work using the GiST indexes,
>  > > but I persistently develop a problem where the system doesn't
>  > > make use of the indexes during the execution of a query.  If
>  > > I use the examples provided here:
>  > >
>  > >   http://wit.mcs.anl.gov/~selkovjr/pg_extensions/
>  > >
>  > > For instance, and I place an elog( DEBUG, "functionname" )
>  > > in each of the GiST accessor functions, I can witness when
>  > > the database is making use of the index.  During the construction
>  > > of the index, I never have a problem, although during query
>  > > execution, it seems that my indices aren't getting used at
>  > > all, and the database is simply searching through all of
>  > > the entries in the database.
>  > >
>  > > This is a terribly frustrating problem that I encountered
>  > > once before, but which mysteriously went away after fiddling
>  > > with the problem for a while.  This time, the problem isn't
>  > > going away, however.  When I trace through the postgres
>  > > application I can see that it at least examines the opclass
>  > > for my specialized data types, and detects that there exists
>  > > an index that could be used, but it seems to decide not to
>  > > make use of it regardless.
>  > >
>  > > Is there an easy way that I can force the use of an index
>  > > during a query?
>  > >
>  > > -David
>  > >
>  > > ----------------------[=========]------------------------
>  > > David T. McWherter                udmcwher@mcs.drexel.edu
>  > >
>  > >    vdiff
>  > > =====
>  > >  /vee'dif/ v.,n.  Visual diff.  The operation offinding
>  > > differences between two files by {eyeball search}.  Theterm
>  > > `optical diff' has also been reported, and is sometimes more
>  > > specifically used for the act of superimposing two nearly identical
>  > > printouts on one another and holding them up to a light to spot
>  > > differences.  Though this method is poor for detecting omissions in
>  > > the `rear' file, it can also be used with printouts of graphics, a
>  > > claim few if any diff programs can make.  See {diff}.
>  > >
>  > > ---------------------------(end of broadcast)---------------------------
>  > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>  > >
>  >
>  >     Regards,
>  >         Oleg
>  > _____________________________________________________________
>  > Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
>  > Sternberg Astronomical Institute, Moscow University (Russia)
>  > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
>  > phone: +007(095)939-16-83, +007(095)939-23-83
>
> ----------------------[=========]------------------------
> David T. McWherter                udmcwher@mcs.drexel.edu
>
> If God had meant for us to be in the Army, we would have been born with
> green, baggy skin.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83