Attached 2nd version of the patches.
On 20.04.2019 16:41, Fabien COELHO wrote:
About the test, I'd suggest to name the result columns, eg "pt to box
dist" and "box to pt dist", otherwise why all is repeated is unclear.
Fixed.
On 02.07.2019 7:01, Tom Lane wrote:
[ warning, drive-by comment ahead ]
Fabien COELHO <coelho@cri.ensmp.fr> writes:
I notice that other distance tests do not test for commutativity. Are they
also not implemented, or just not tested? If not implemented, I'd suggest
to add them in the same batch.
Yeah ... just looking at operators named <->, I see
regression=# select oid, oid::regoperator, oprcom, oprcode from pg_operator where oprname = '<->';oid | oid | oprcom | oprcode
------+----------------------+--------+--------------------------- 517 | <->(point,point) | 517 | point_distance 613 | <->(point,line) | 0 | dist_pl 614 | <->(point,lseg) | 0 | dist_ps 615 | <->(point,box) | 0 | dist_pb 616 | <->(lseg,line) | 0 | dist_sl 617 | <->(lseg,box) | 0 | dist_sb 618 | <->(point,path) | 0 | dist_ppath 706 | <->(box,box) | 706 | box_distance 707 | <->(path,path) | 707 | path_distance 708 | <->(line,line) | 708 | line_distance 709 | <->(lseg,lseg) | 709 | lseg_distance 712 | <->(polygon,polygon) | 712 | poly_distance1520 | <->(circle,circle) | 1520 | circle_distance1522 | <->(point,circle) | 3291 | dist_pc3291 | <->(circle,point) | 1522 | dist_cpoint3276 | <->(point,polygon) | 3289 | dist_ppoly3289 | <->(polygon,point) | 3276 | dist_polyp1523 | <->(circle,polygon) | 0 | dist_cpoly1524 | <->(line,box) | 0 | dist_lb5005 | <->(tsquery,tsquery) | 0 | pg_catalog.tsquery_phrase
(20 rows)
It's not clear to me why to be particularly more excited about
<->(box, point) than about the other missing cases here.
regards, tom lane
The original goal was to add support of ordering by distance to point to
all geometric opclasses. As you can see, GiST and SP-GiST box_ops has no
distance operator while more complex circle_ops and poly_ops have it:
SELECT amname, opcname, amopopr::regoperator AS dist_opr
FROM pg_opclass LEFT JOIN pg_amop ON amopfamily = opcfamily AND amoppurpose = 'o', pg_am, pg_type
WHERE opcmethod = pg_am.oid AND opcintype = pg_type.oid AND typcategory = 'G'
ORDER BY 1, 2;
amname | opcname | dist_opr
--------+-------------------+--------------------brin | box_inclusion_ops | gist | box_ops | gist | circle_ops | <->(circle,point)gist | point_ops | <->(point,point)gist | poly_ops | <->(polygon,point)spgist | box_ops | spgist | kd_point_ops | <->(point,point)spgist | poly_ops | <->(polygon,point)spgist | quad_point_ops | <->(point,point)
(9 rows)
We could use commuted "const <-> var" operators for kNN searches, but the
current implementation requires the existence of "var <-> const" operators, and
order-by-op clauses are rebuilt using them (see match_clause_to_ordering_op()
at /src/backend/optimizer/path/indxpath.c).
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company