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
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. |