I am trying to use GIST index to accelerate a geometric query (find points within a rectangular region. The query refuses to use the index; it does table scan. But if I convert the point to a (zero size) box, then find overlapping boxes, index is used.

Note: these are geometric locations, not geographic. I do not have PostGIS installed.

Steps to recreate.

1. Create Table:

CREATE TABLE testgeom ( boxcol BOX NOT NULL);

2. Load random data.
3. Create index on geometry column:

CREATE INDEX testgeom_idx ON testgeom USING GIST(boxcol);

4. Run query a) which ignores index, and query b) which use it:

    a) EXPLAIN ANALYZE SELECT * 
        FROM testgeom WHERE point(1,1) <@ boxcol;
    b) EXPLAIN ANALYZE SELECT *
        FROM testgeom WHERE box(point(1,1),point(1,1)) && boxcol;
   

The second query would seem a solution but it has to be doing much more calculation than just point in box. Is there a better way?

I am trying to use GIST index to accelerate a geometric query (find points within a rectangular region. The query refuses to use the index; it does table scan. But if I convert the point to a (zero size) box, then find overlapping boxes, index is used.

Note: these are geometric locations, not geographic. I do not have PostGIS installed.

Steps to recreate.

1. Create Table:

CREATE TABLE testgeom ( boxcol BOX NOT NULL);

2. Load random data.
3. Create index on geometry column:

CREATE INDEX testgeom_idx ON testgeom USING GIST(boxcol);

4. Run query a) which ignores index, and query b) which use it:

    a) EXPLAIN ANALYZE SELECT * 
        FROM testgeom WHERE point(1,1) <@ boxcol;
    b) EXPLAIN ANALYZE SELECT *
        FROM testgeom WHERE box(point(1,1),point(1,1)) && boxcol;
   

The second query would seem a solution but it has to be doing much more calculation than just point in box. Is there a better way?

• postgresql

1 Answer 1

The type cast is necessary because the index is on a BOX, not a POINT, and PostgreSQL does not perform implicit casts from POINT to BOX nor is the operator <@ (point, box) available as part of the operator class box_ops. Although it won't change the amount of calculation required, the conversion can be more succinctly expressed as POINT(1,1)::BOX instead of BOX(POINT(1,1),POINT(1,1)). There is also no need to change the operator from <@ to && since inclusion of a point within a box is equivalent to a point overlapping a box and either operator will use the index.