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
WHERE FB.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
WHERE FB.feature_id = TB.feature_id
AND FB.boundout IS FALSE
AND contains(FB.boundbox,W.geocode)
AND contains(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
WHERE FB.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];
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
