Thread: Custom geometry, why slow?

Custom geometry, why slow?

From
Paul Matthews
Date:
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),
--OPERATOR8  <@  (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?


Re: Custom geometry, why slow?

From
Tom Lane
Date:
Paul Matthews <plm@netspace.net.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