ghiureai wrote: > 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)" 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
