Re: Geometric Elimination - Mailing list pgsql-hackers

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];