Lists: | pgsql-hackers |
---|
From: | Paul Matthews <plm(at)netspace(dot)net(dot)au> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Custom geometry, why slow? |
Date: | 2009-08-14 03:13:16 |
Message-ID: | 4A84D64C.6000007@netspace.net.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
The story so far ... The provide polygon@>point routine does not work
correctly when the points are close to the boundary. So we implemented a
custom contains(poly,point) function. In order to stop all points being
checked against all polygons, a separate bounding box is maintained. So
the query has sections looking like :
boundbox @> box( thepoint, thepoint ) AND
contains(boundary,thepoint)
You will notice that each point to be checked has to be promoted to a
degenerate box. Working on the assumption that there is a cost
associated with this (ie pmalloc), and we will be passing 100's of
millions of points though this in a single transaction, streaming this
is important. At any rate it looked kludgy. The goal is provide :
boundbox @> thepoint AND
contains(boundary,thepoint)
So the whole family of "point op box" functions where provided (except
for point <@ box) which already exists. The operators have been created.
And the operators added to the box_ops operator family. Samples below :
CREATE OR REPLACE FUNCTION leftof(box,point) RETURNS boolean
LANGUAGE C IMMUTABLE STRICT
AS 'contains.so', 'box_point_leftof';
..etc...
DROP OPERATOR IF EXISTS <<(box,point);
CREATE OPERATOR << (
LEFTARG = box,
RIGHTARG = point,
PROCEDURE = leftof,
RESTRICT = positionsel,
JOIN = positionjoinsel
);
...etc...
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 8 <@ (point,box),
OPERATOR 9 &<| (box,point),
OPERATOR 10 <<| (box,point),
OPERATOR 11 |>> (box,point),
OPERATOR 12 |&> (box,point);
The problem is, according to EXPLAIN, it still wants to do a sequential
scan and not use the index. Any pointers as to why?
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Paul Matthews <plm(at)netspace(dot)net(dot)au> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Custom geometry, why slow? |
Date: | 2009-08-14 14:33:16 |
Message-ID: | 8652.1250260396@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Paul Matthews <plm(at)netspace(dot)net(dot)au> writes:
> The problem is, according to EXPLAIN, it still wants to do a sequential
> scan and not use the index. Any pointers as to why?
Can you force it with enable_seqscan = off? If so, then positionsel
isn't estimating a small enough number of matches to make an indexscan
look useful. (Which, for a "left of" operator, is hardly surprising.)
If not, there's something wrong with your opfamily declarations.
regards, tom lane