Selectivity estimators for <@(spoint, scircle) and spoint_dwithin(spoint, spoint, float8)

[df7cb]

This implements restriction selectivity estimation for the <@ @> !<@ !@> family of operators on spoint and scircle. The selectivity is estimated to be (area of sphere circle) / (4 pi).

Queries like select * from sky where sky.star <@ scircle(const, radius) will be able to properly estimate if using an index is appropriate depending on the size of radius.

Secondly, a function spoint_dwithin(p1 spoint, p2 spoint, radius float8) is added that effectively returns p1 <-> p2 <= radius. But other than this two-operator expression, it has GIST index support so the optimizer can rewrite it to either p1 <@ scircle(p2, radius) or p2 <@ scircle(p1, radius), i.e. it is symmetric in the first two arguments.

This allows efficient matching queries without the user having to encode the join ordering in the query.

On PostgreSQL 10/11, the spoint_dwithin function is created, but without the GIST support since that only appeared in PG12.

The file expected/selectivity_1.out is used on PG10/11; it has <@ flipped around to @> in some plans.

[esabol]

This is an awesome contribution! Thank you!

[df7cb]

Am 6. Oktober 2023 21:41:54 MESZ schrieb Ed Sabol @.***>:

@esabol commented on this pull request.

@@ -241,8 +248,11 @@ pg_sphere--1.2.3--1.3.0.sql: pgs_brin.sql.in pg_sphere--1.3.0--1.3.1.sql: cat @.*** > $@

-pg_sphere--1.3.1--1.3.2.sql:

• cat @.*** > $@ +ifeq ($(has_support_functions),y) +pg_sphere--1.3.1--1.3.2.sql: pgs_gist_support.sql.in +endif +pg_sphere--1.3.1--1.3.2.sql: pgs_circle_sel.sql.in
• cat @.*** $^ > $@

I'm not sure, but I wonder if lines 251-255 should be the following instead?

ifeq ($(has_support_functions),y)
pg_sphere--1.3.1--1.3.2.sql: pgs_gist_support.sql.in pgs_circle_sel.sql.in
else
pg_sphere--1.3.1--1.3.2.sql: pgs_circle_sel.sql.in
endif
  cat ***@***.*** $^ > $@

-- Reply to this email directly or view it on GitHub: https://github.com/postgrespro/pgsphere/pull/80#pullrequestreview-1662061892 You are receiving this because you authored the thread.

Message ID: @.***> It does works that way, $^ collects all dependencies.

[esabol]

In case anyone is interested in comparing selectivity estimators, similar efforts are in progress over in the q3c repo as well:

https://github.com/segasai/q3c/issues/30 https://github.com/segasai/q3c/pull/35/files

[df7cb]

Rebased.

[vitcpp]

@df7cb Thank you for the PR!

[df7cb]

@vitcpp @esabol @Alena0704 thanks for the reviews!