Re: Geometric Elimination

From: Paul Matthews <plm(at)netspace(dot)net(dot)au>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Geometric Elimination
Date: 2009-08-21 11:42:57
Message-ID: 4A8E8841.5030103@netspace.net.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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
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];

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2009-08-21 11:43:45 Index-only quals
Previous Message Dimitri Fontaine 2009-08-21 11:10:49 Re: WIP: generalized index constraints