Thread: Geometric Elimination
Trying to solve this problem by using a process of elimination. All
works fine until the comment below is removed.
ALTER OPERATOR FAMILY box_ops USING GiST ADD OPERATOR 1 << (box,point), OPERATOR 2 &< (box,point), OPERATOR 3 &&
(box,point),OPERATOR 4 &> (box,point), OPERATOR 5 >> (box,point),
-- OPERATOR 7 @> (box,point), OPERATOR 9 &<| (box,point), OPERATOR 10 <<| (box,point), OPERATOR 11 |>>
(box,point);
Ah! So operator @> is wrong.
DROP OPERATOR IF EXISTS @>(box,point);
CREATE OPERATOR @> ( LEFTARG = box, RIGHTARG = point, PROCEDURE = contains, COMMUTATOR = <@, RESTRICT =
contsel,JOIN = contjoinsel
);
No, all seems fine here. Maybe the definition of the function is incorrect
CREATE OR REPLACE FUNCTION contains(box,point) RETURNS boolean
LANGUAGE C IMMUTABLE STRICT
AS 'contains.so', 'box_point_contains';
The C function? No it seems OK as well. What am I missing? It must be
completely obvious. Someone is laughing out there. Put me out of my
misery please!
/** Box contains point. box @> point.*/
Datum box_point_contains(PG_FUNCTION_ARGS)
{ BOX *box = PG_GETARG_BOX_P(0); Point *point = PG_GETARG_POINT_P(1); int isin = point->x >= box->low.x &&
point->x <= box->high.x && point->y >= box->low.y && point->y <= box->high.y;
PG_RETURN_BOOL(isin);
}
On Fri, Aug 21, 2009 at 08:28:05PM +1000, Paul Matthews wrote: > Trying to solve this problem by using a process of elimination. All > works fine until the comment below is removed. I haven't completely understood what you're trying to do, but I have a few points. - I don't see any definition of an operator class, just the family, which doesn't seem to make any sense to me. - Does it work if you replace the use of the operator with the equivalent function call (contains)? - Check for differences in the explain output, that should reveal any implicit casts that may be getting in your way. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Martijn van Oosterhout wrote:
> I haven't completely understood what you're trying to do
>
Putting in place the missing 'box op point' and 'point op box'
operators. The problematic queries are at the bottom of the email.
> - I don't see any definition of an operator class, just the family,
> which doesn't seem to make any sense to me.
>
I am working on the assumption that the Box and Point class already have
operator classes. Otherwise how would all the existing Box and Point
operators work? From my limited understanding of the source code it's in
postgres.bki round about line 2208 and 2211. (It there a better
interface to this information?).
> - Does it work if you replace the use of the operator with the
> equivalent function call (contains)?
>
Good idea. Yes. It does work. As such, we can assume that the C function
and the CREATE FUNCTION are OK.
> - Check for differences in the explain output, that should reveal any
> implicit casts that may be getting in your way.
>
The EXPLAIN does not show any explicit casts occurring.
-- This works
-- 17 seconds
SELECT W.geocode, F.state, F.code, F.name
FROM work as W, features as F, boundary as TB
WHERE TB.feature_id = F.feature_id AND TB.boundout IS TRUE AND TB.boundbox @> box(W.geocode,W.geocode) AND
contains(TB.boundary,W.geocode)AND (TB.feature_id) NOT IN ( SELECT feature_id FROM boundary as FB
WHEREFB.feature_id = TB.feature_id AND FB.boundout IS FALSE AND FB.boundbox @> box(W.geocode,W.geocode)
AND contains(FB.boundary,W.geocode) )
ORDER BY W.geocode[0], W.geocode[1];
-- This works
-- 39 seconds
SELECT W.geocode, F.state, F.code, F.name
FROM work as W, features as F, boundary as TB
WHERE TB.feature_id = F.feature_id AND TB.boundout IS TRUE AND contains(TB.boundbox,W.geocode) AND
contains(TB.boundary,W.geocode)AND (TB.feature_id) NOT IN ( SELECT feature_id FROM boundary as FB
WHEREFB.feature_id = TB.feature_id AND FB.boundout IS FALSE AND contains(FB.boundbox,W.geocode)
ANDcontains(FB.boundary,W.geocode) )
ORDER BY W.geocode[0], W.geocode[1];
-- This fails.
-- Returns empty set
SELECT W.geocode, F.state, F.code, F.name
FROM work as W, features as F, boundary as TB
WHERE TB.feature_id = F.feature_id AND TB.boundout IS TRUE AND TB.boundbox @> W.geocode AND
contains(TB.boundary,W.geocode)AND (TB.feature_id) NOT IN ( SELECT feature_id FROM boundary as FB
WHEREFB.feature_id = TB.feature_id AND FB.boundout IS FALSE AND FB.boundbox @> W.geocode AND
contains(FB.boundary,W.geocode) )
ORDER BY W.geocode[0], W.geocode[1];
Paul Matthews <plm@netspace.net.au> writes:
> The C function? No it seems OK as well. What am I missing?
Did you teach the opclass's consistent() function about these new
operators?
regards, tom lane
On Fri, Aug 21, 2009 at 09:42:57PM +1000, Paul Matthews wrote: > Martijn van Oosterhout wrote: > > I haven't completely understood what you're trying to do > > > Putting in place the missing 'box op point' and 'point op box' > operators. The problematic queries are at the bottom of the email. If that's all you're doing, why all the fussing with indexes. > > - I don't see any definition of an operator class, just the family, > > which doesn't seem to make any sense to me. > > > I am working on the assumption that the Box and Point class already have > operator classes. Otherwise how would all the existing Box and Point > operators work? From my limited understanding of the source code it's in > postgres.bki round about line 2208 and 2211. (It there a better > interface to this information?). The box type has an operator class, for boxes. There is no operator class for points, it could clearly be done, it just hasn't. Operator classes are *only* needed for index support. If you don't want index support, don't set them up. For catalogs I usually get go the the anoncvs interface for the raw data, for example: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/pg_opclass.h?rev=1.85 http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/pg_amop.h?rev=1.90 If you want index support, you need to go the whole way, and setup an appropriate operator class for (box,point). > > - Check for differences in the explain output, that should reveal any > > implicit casts that may be getting in your way. > > > The EXPLAIN does not show any explicit casts occurring. It would be helpful if you pasted the actual EXPLAIN output. The last two really should be the same, so what's the difference in explain output? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Martijn van Oosterhout <kleptog@svana.org> writes:
> If you want index support, you need to go the whole way, and setup an
> appropriate operator class for (box,point).
No, I think he's doing the right thing by adding these cross-type
operators "loose" in the opfamily. An operator class is the subset
of an opfamily that's *essential* to the functioning of an index
on a particular datatype, and cross-type ops almost by definition
are not that. I suspect the missing piece is to add cases to the
relevant consistent() function(s).
regards, tom lane