Thread: GIST index (polygon, point)

Hi List,

I have a short description bellow from Dev team regarding the behaviour of gist index on the polygon column, looking to get some  feedback  from you:

".... I was expecting the <@(point,polygon) and @>(polygon,point) to be indexable but they are not. see bellow query output ,
the column is a polygon and the index is a gist index on the polygon column; my understanding of the above query is that it says which operators would cause that index to be used

This SQL shows which operators are indexable:SELECT
 pg_get_indexdef(ss.indexrelid, (ss.iopc).n, TRUE) AS index_col,
 amop.amopopr::regoperator AS indexable_operator
FROM pg_opclass opc, pg_amop amop,
 (SELECT indexrelid, information_schema._pg_expandarray(indclass) AS iopc
  FROM pg_index
  WHERE indexrelid = 'caom2.Plane_energy_ib'::regclass) ss
WHERE amop.amopfamily = opc.opcfamily AND opc.oid = (ss.iopc).x
ORDER BY (ss.iopc).n, indexable_operator;

We run  the SQL  in PG 9.5.3 and PG 10.2 we  the same result: only polygon vs polygon is indexable (except the last entry which is distance operator).

The work around for us was to change interval-contains-value from polygon-contains-point (@> or <@ operator) to polygn-intersects-really-small-polygon (&&) in order to use the index, but I was quite surprised that contains operators are not indexable!

Note that this is using the built in polygon and not pgsphere (spoly)"

thank you

Isabella

ghiureai wrote:
> I have a short description bellow from Dev team regarding the behaviour of gist index on the polygon column, looking
toget some  feedback  from you:
 
>
> ".... I was expecting the <@(point,polygon) and @>(polygon,point) to be indexable but they are not. see bellow query
output,
 
> the column is a polygon and the index is a gist index on the polygon column; my understanding of the above query is
thatit says which operators would cause that index to be used
 
>
> This SQL shows which operators are indexable:SELECT
>  pg_get_indexdef(ss.indexrelid, (ss.iopc).n, TRUE) AS index_col,
>  amop.amopopr::regoperator AS indexable_operator
> FROM pg_opclass opc, pg_amop amop,
>  (SELECT indexrelid, information_schema._pg_expandarray(indclass) AS iopc
>   FROM pg_index
>   WHERE indexrelid = 'caom2.Plane_energy_ib'::regclass) ss
> WHERE amop.amopfamily = opc.opcfamily AND opc.oid = (ss.iopc).x
> ORDER BY (ss.iopc).n, indexable_operator;
>
> We run  the SQL  in PG 9.5.3 and PG 10.2 we  the same result: only polygon vs polygon is indexable (except the last
entrywhich is distance operator).
 
> The work around for us was to change interval-contains-value from polygon-contains-point (@> or <@ operator) to
> polygn-intersects-really-small-polygon (&&) in order to use the index, but I was quite surprised that contains
operatorsare not indexable!
 
> Note that this is using the built in polygon and not pgsphere (spoly)"

That sounds about right.

You could use a single-point polygon like '((1,1))'::polygon
and the <@ or && operator.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com