GIST index (polygon, point)

From: ghiureai <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: GIST index (polygon, point)
Date: 2018-03-05 16:18:22
Message-ID: f3102e7a-a1bd-026c-fbbc-9a50d9c7b68b@nrc-cnrc.gc.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Daulat Ram 2018-03-06 08:01:54 Please help
Previous Message John van Breda 2018-03-05 15:45:09 Slow index scan backward.