geo: implement ST_GeomFromGeoHash({text,int4}) -> geometry #48806
Labels
A-geometry-creation-builtins
Builtins which have geometry as a return statement.
C-enhancement
Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
E-easy
Easy issue to tackle, requires little or no CockroachDB experience
X-nostale
Marks an issue/pr that should be ignored by the stale bot
Comments
otan
added
A-geometry-creation-builtins
craig bot
pushed a commit
that referenced
this issue
Aug 21, 2020
46407: kvcoord: small fixes r=andreimatei a=andreimatei See individual commits. 52740: sql: owning a schema gives privilege to drop tables in schema r=RichardJCai a=RichardJCai sql: owning a schema gives privilege to drop tables in schema Release note (sql change): Schema owners can drop tables inside the schema without explicit DROP privilege on the table. Fixes #51931 53127: builtins: use ST_Union as aggregate r=rytaft,sumeerbhola a=otan Add the ST_Union aggregate, removing the two-argument version temporarily as we cannot currently have an aggregate and non-aggregate at the same time. This is ok since we haven't released yet, and from reading it seems more likely people will use the aggregate version. Release note (sql change): Implement the ST_Union builtin as an aggregate. The previous alpha-available ST_Union for two arguments is deprecated. 53162: builtins: implement ST_GeomFromGeoHash/ST_Box2DFromGeoHash r=sumeerbhola a=otan Resolves #48806 Release note (sql change): Implemented the ST_GeomFromGeoHash and ST_Box2DFromGeoHash builtins. 53181: sql/kv: avoid expensive heap allocations with inverted joins r=nvanbenschoten a=nvanbenschoten This PR contains three optimizations that reduce heap allocations by inverted join operations. These were found by profiling the following geospatial query: ```sql SELECT Count(census.blkid), Count(subways.name) FROM nyc_census_blocks AS census JOIN nyc_subway_stations AS subways ON ST_Intersects(subways.geom, census.geom); ``` Combined, these three commits speed up the query by a little over **3%**. ``` name old ms new ms delta Test/postgis_geometry_tutorial/nyc 139 ±12% 135 ±11% -3.04% (p=0.000 n=241+243) ``` #### sql/rowexec: avoid heap allocations in batchedInvertedExprEvaluator This commit restructures the slice manipulation performed in `(*batchedInvertedExprEvaluator).init` to avoid heap allocations. Before the change, this method used to contain the first and fifth most expensive memory allocations by total allocation size (`alloc_space`) in a geospatial query of interest: ``` ----------------------------------------------------------+------------- 3994.06MB 100% | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*invertedJoiner).readInput /go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/inverted_joiner.go:418 3994.06MB 10.70% 10.70% 3994.06MB 10.70% | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*batchedInvertedExprEvaluator).init /go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/inverted_expr_evaluator.go:366 ----------------------------------------------------------+------------- ----------------------------------------------------------+------------- 954.07MB 100% | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*invertedJoiner).readInput /go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/inverted_joiner.go:418 954.07MB 4.61% 41.71% 954.07MB 4.61% | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*batchedInvertedExprEvaluator).init /go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/inverted_expr_evaluator.go:409 ----------------------------------------------------------+------------- ``` The largest offender here was the `routingSpans` slice, which made no attempt to recycle memory. The other offender was the `pendingSpans` slice. We made an attempt to recycle the memory in this slice, except we truncated it from the front instead of the back, so we ended us not actually recycling anything. This commit addresses this in two ways. First, it updates the `pendingSpans` slice to point into the `routingSpans` slice so the two can share memory. `pendingSpans` becomes a mutable window into `routingSpans`, so it no longer requires its own heap allocation. Next, the commit updates the memory recycling to recycle `routingSpans` instead of `pendingSpans`. Since we never truncate `routingSpans` from the front, the recycling now works. With these two changes, the two heap allocations, which used to account for **15.31%** of total allocated space combined drops to a single heap allocation that accounts for only **2.43%** of total allocated space on the geospatial query. ``` ----------------------------------------------------------+------------- 182.62MB 100% | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*invertedJoiner).readInput /go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/inverted_joiner.go:418 182.62MB 2.43% 58.36% 182.62MB 2.43% | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*batchedInvertedExprEvaluator).init /go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/inverted_expr_evaluator.go:366 ----------------------------------------------------------+------------- ``` #### sql: properly copy span slices This commit fixes two locations where we not optimally performing copies from one slice to another. In `invertedJoiner.generateSpans`, we were not pre-allocating the destination slice and were using append instead of direct assignment, which is measurably slower due to the extra writes to the slice header. In `getProtoSpans`, we again were appending to a slice with zero length and sufficient capacity instead of assigning to a slice with sufficient length. #### kv: check for expensive logging before VEventf in appendRefreshSpans Before this change, we called VEventf directly with the refresh span corresponding to each request in a batch. This caused each span object to allocate while passing through an `interface{}`. This could be seen in heap profiles, where it accounted for **1.89%** of total allocated memory (`alloc_space`) in a geospatial query of interest: ``` ----------------------------------------------------------+------------- 142.01MB 100% | github.com/cockroachdb/cockroach/pkg/roachpb.(*BatchRequest).RefreshSpanIterate /go/src/github.com/cockroachdb/cockroach/pkg/roachpb/batch.go:407 142.01MB 1.89% 68.71% 142.01MB 1.89% | github.com/cockroachdb/cockroach/pkg/kv/kvclient/kvcoord.(*txnSpanRefresher).appendRefreshSpans.func1 /go/src/github.com/cockroachdb/cockroach/pkg/kv/kvclient/kvcoord/txn_interceptor_span_refresher.go:510 ----------------------------------------------------------+------------- ``` This commit adds a guard around the VEventf to avoid the allocation. 53196: sqlliveness/slstorage,timeutil: various improvements r=spaskob a=ajwerner See individual commits. This PR addresses some anxieties I had about the implementation of slstorage. In particular, it makes the testing deterministic by giving it a new table independent of the host cluster's state and allows injecting time. It then changes the GC to be less frequent and instead relies on failing out sessions when it determines they are expired. This is important to avoid spinning between when a session is deemed expired and when it would later be deleted by a gc interval. It also jitters that GC interval to avoid problems due to contention in larger clusters. 53214: serverpb: add docstrings to `HotRangesRequest` and `HotRangesResponse` r=mjibson a=knz This is part of a project to document the public HTTP APIs. @mjibson you'll notice in this PR that the doc generator doesn't deal well with newline characters in docstrings. Co-authored-by: Andrei Matei <[email protected]> Co-authored-by: richardjcai <[email protected]> Co-authored-by: Oliver Tan <[email protected]> Co-authored-by: Nathan VanBenschoten <[email protected]> Co-authored-by: Andrew Werner <[email protected]> Co-authored-by: Raphael 'kena' Poss <[email protected]>
otan
added
the
X-nostale
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Implement
ST_GeomFromGeoHashon arguments {text,int4}, which should adopt PostGIS behaviour.Observers: Please react to this issue if you need this functionality.
For Geometry builtins, please do the following:
pkg/geo. Add exhaustive unit tests here - you can run through example test cases and make sure that PostGIS and CRDB return the same result within a degree of accuracy (1cm for geography).pkg/sql/sem/builtins/geo_builtins.go. Note that we currently do not support optional arguments, so we define functions that have optional arguments once without the optional argument (using the default value in the optional position), and once with the optional argument.pkg/sql/logictest/testdata/logic_test/geospatialto call this functionality at least once. You can callmake testbaselogic FILES='geospatial' TESTFLAGS='-rewrite'to regenerate the output. Tests here should just ensure the builtin is linked end to end (your exhaustive unit tests go the above mentioned packages!).make buildshort. You can also play with it by calling./cockroach demo --emptyafterwards.You can follow #48441 for an example PR.
🤖 This issue was synced with a spreadsheet by gsheets-to-github-issues by otan on 2023-09-03T23:14:40Z. Changes to titles, body and labels may be overwritten.
The text was updated successfully, but these errors were encountered: