Re: GiST indexing problems...

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(at)mcs(dot)drexel(dot)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(at)postgresql(dot)org
> >
>
> Regards,
> Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83

----------------------[=========]------------------------
David T. McWherter udmcwher(at)mcs(dot)drexel(dot)edu

If God had meant for us to be in the Army, we would have been born with
green, baggy skin.