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
