pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedGIST index (polygon, point)
2+ messages / 2 participants
[nested] [flat]
* GIST index (polygon, point)
@ 2018-03-05 16:18 ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 2+ messages in thread
From: ghiureai @ 2018-03-05 16:18 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: GIST index (polygon, point)
@ 2018-03-06 09:59 Laurenz Albe <laurenz.albe@cybertec.at>
parent: ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2018-03-06 09:59 UTC (permalink / raw)
To: ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>; pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-03-06 09:59 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-05 16:18 GIST index (polygon, point) ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
2018-03-06 09:59 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox