Spatial query performance with/without indexes

Hi there,

I’ve been playing with the spatial data features of cockroachdb 20.2 and I’m hoping someone could shed some light into this scenario I’ve encountered.

We have a users table, created using the following migrations:

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  lat FLOAT,
  lng FLOAT
);
ALTER TABLE users ADD COLUMN coordinates GEOGRAPHY;
UPDATE users SET coordinates = st_makepoint(lng, lat);
CREATE INDEX users_coordinates_idx ON users USING GIST(coordinates);

The table currently has a bit over 400k rows.

I need to query all users within a distance of some coordinates. The first option I’ve tried was using the st_dwithin function:

SELECT users.id, users.lat, users.lng
FROM users 
WHERE st_dwithin(users.coordinates, st_makepoint(-2.25, 53.77)::geography, 15000)
ORDER BY users.id
LIMIT 10 OFFSET 0;

                   id                  |    lat    |    lng
---------------------------------------+-----------+------------
...
(10 rows)

Time: 930ms total (execution 928ms / network 2ms)

The explain plan for this query shows it’s using the index:


                 tree                 |        field        |                                      description
--------------------------------------+---------------------+-----------------------------------------------------------------------------------------
                                      | distribution        | full
                                      | vectorized          | false
  limit                               |                     |
   │                                  | count               | 10
   └── filter                         |                     |
        │                             | filter              | st_dwithin(coordinates, '0101000020E610000000000000000002C0C3F5285C8FE24A40', 15000.0)
        └── sort                      |                     |
             │                        | order               | +id
             └── index join           |                     |
                  │                   | table               | users@primary
                  └── inverted filter |                     |
                       │              | inverted column     | coordinates_inverted_key
                       │              | num spans           | 17
                       └── scan       |                     |
                                      | estimated row count | 45520
                                      | table               | users@users_coordinates_idx
                                      | spans               | 17 spans

Then I tried with the st_distancespheroid function to see if I could get a better performance (at first I didn’t know this function doesn’t use the index):

SELECT users.id, users.lat, users.lng
FROM users
WHERE st_distancespheroid(users.coordinates::geometry, st_makepoint(-2.25, 53.77)) < 15000
ORDER BY users.id
LIMIT 10 OFFSET 0;

                   id                  |    lat    |    lng
---------------------------------------+-----------+------------
...
(10 rows)

Time: 302ms total (execution 301ms / network 1ms)

However running EXPLAIN for this query, it shows it doesn’t use the index:

       tree      |        field        |                                            description
-----------------+---------------------+-----------------------------------------------------------------------------------------------------
                 | distribution        | full
                 | vectorized          | true
  limit          |                     |
   │             | count               | 10
   └── filter    |                     |
        │        | filter              | st_distancespheroid(coordinates::GEOMETRY, '010100000000000000000002C0C3F5285C8FE24A40') < 15000.0
        └── scan |                     |
                 | estimated row count | 409681
                 | table               | users@primary
                 | spans               | FULL SCAN

I’m not sure why the query that doesn’t use the index is roughly 3x faster than the one that uses it.

Any help is appreciated,

Thanks

@vtavares, it would be helpful if you could prepend EXPLAIN ANALYZE to each of these queries, run them, and then share here the resulting links.

Thank you for raising the issue here!

Mike

Hi @mgoddard , thanks for your reply and sorry for the delay on responding.

These are the results of EXPLAIN ANALYZE as requested:

explain analyze
SELECT users.id, users.lat, users.lng
FROM users 
WHERE st_dwithin(users.coordinates, st_makepoint(-2.25, 53.77)::geography, 20000)
ORDER BY users.id
LIMIT 10 OFFSET 0;

  automatic |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        url
------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    true    | https://cockroachdb.github.io/distsqlplan/decode.html#eJyUlM9u4zYQxu99isFcNkEZmdQfK8uTnKy8ceHEqW2gTesg0JpslqhFqiTddRDk3QtJ2dTGxorjA0Fy5qM5Pw2_R3T_rJBj_vv1eDC6gsHVYHzzRw5Hn0az-ezX8THM8nF-Poe1k9YFSpDn2arwL1N9D8Pp5LJdwm8X-TQH5-_EN-W_Kn3UZi2NsULpwktH6mhZ_C0ro7Q_OgmDMCGQREGaHnP-OZ98ng6uL24IhJRSegyT6ad8Cmc3L5eA8ehyNAdGYTIczvI5UCSojZBXRSkd8j-RIcEIbwlW1iylc8bW249N0khskFOCSldrX2_fElwaK5E_old-JZHjlTkxVS9GgkL6Qq2atCeCZu3_Fzlf3EvkyRPZOph1HzwvvqzkVBZC2h7dOR6b6rJmvNuidafEBgnOqkI7Dr0Fni0Wm7_ERbZYbCjdP-DJoblsgVBoAawPxn-V1iHBydpzyBjJEiRozTcHVhaCQ5REaY3F-WK1Aq9KyaFPA5qwspZ9efDyJTVgcKnOcB839h5uI_2vtF6KoVp5aaXtsV143-P5prJgNGSMg6uRgfOF9bxBEaVJjYLWZe8bEKQWh6bX5PahO4BbGHxkYcOtLDZQytLYh7rJBYc4SDvhhe-B94tR-rnnwtd6rrKqLOzD9mePSBYf9PFZRIMojpsqlBZyA68I2sC2LAw-9vt9lrq9FUbvqXBmrJe2F-1Wl7GfkWDbMnzbkrKYwAfKKKsNJqR5v5ns_sJzeh4Nk_A0OT8d5mE8iOkH_qM9BZRzPhxPBvPT422AIcnq24xVqTww-jbIKGBpnKbhq-1w-sZbindgvWFuU-kqo508yN3o0y1BKe5la6DOrO1SXluzbP6mXU4aXbMhpPNtNGkXI92G6gtui1mnOOwWh53iqFscdYrjbnHcKaa74gZiw_PZGJ3Uvu7972_79umn_wIAAP__h2pUsg==
(1 row)

Time: 3.323s total (execution 3.322s / network 0.001s)
explain analyze
SELECT users.id, users.lat, users.lng
FROM users
WHERE st_distancespheroid(users.coordinates::geometry, st_makepoint(-2.25, 53.77)) < 20000
ORDER BY users.id
LIMIT 10 OFFSET 0;

  automatic |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              url
------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    true    | https://cockroachdb.github.io/distsqlplan/decode.html#eJzUVd1u2zYUvt9TEOemCUYp_JVkXdlN5c2YE3e2gS2bjYEWmUSYLGoijbUI8lh7gT3ZIDlL48QuXLS9qK_Ew_Mdfud8H47vwP1VQgrZr2_Hg9ElGlwOxle_ZejkzWg2n_08PkWzbJydz9HGmcaFhcYPX6Xyj5_VDRpOJxfbI_rlx2yaIef_0IXzqsqNq29NYwt9sk3PrW10USlvXJr-kE0usvn0CreAtfrT1Lao_EnAQiYxkjyM49NTtNgQwnPECCEETaZvsil6ffVICY1HF6M5ogRNhsNZNkcEMFRWm0u1Ng7S34ECBgYYOCwx1I3NjXO2aa_uusSRfgcpwVBU9ca34SWG3DYG0jvwhS8NpDBXq9JMjdKmOWvraeNVUXblOyL9uinWqnkPGGa1qlyKzhbAVY9HRiRBLGUvkJImgTJcBiTnq7zHSXRNrhcQnC0gUZFSiebBihkeyGtBglWkkoCscipyLeiKRgtAqtKIIutvTQMYhkXpTZPuHXZfPp3uK0IJJS9_7Jyc86FkiTxPhhkTA0FepR-Au6MPSZqmw_FkME8Aw2TjU9SnuM9xX-C-BAzjYl14RNv5N_ZvhxqjdIpE1AacV2WJfLE2KUpCFq8dYFi99-b_NMoJ-ql43UaVz2-NQ3bj6_YRDhj8pi6fhGLAYN6ZfOMLWz1UpSIkCT9Yd3mPYQt_UNh5dWMgpff4gAs-iG8bbRqjd2Xv0-9heb_HKpc2sPUZ381-HBfDfb47q-f9ipf9dnkvGo7__ccd7Ivt9EWPdzc5yt3B0fb-Zowa0-iZUWkSEiE_warHSseSUEj6mV5lx2vKjt1Yx-2hVvzrJOpxYkSgcyYDyWIS9CgVARE5oTyiWjD2DYkvmXgmPgvj5Gto3wtlxL7gmtoj_dS42lbO7Kh-qDJpd5jRN2a785zdNLl529i8e2Z7nHS4LqCN89tbuj2Mqu6qI_gUTD8KFjtg0rHpiH3GOgyT9m9l-ZwI-5QuviYR_oWI0D1E9vBIwoh3NJb33_0XAAD__yZcA8k=
(1 row)

Time: 54ms total (execution 53ms / network 1ms)

Thanks

@vtavares , thank you for sharing that. That output let to a request from our team for a second round of diagnostics. Would you kindly run each of these but with explain (opt, verbose) pre-pended to the query this time?

Your assistance here is super valuable.

Best,

Mike

@mgoddard the results were too verbose to paste here on the reply, so I’ve created a gist for it:

thanks again for the assistance.

Thank you, @vtavares . From the output you shared, the team was able to determine that the estimated row count for the primary index scan is way too high, and they are working to improve this, but it may not make it into the 21.1 release. They suggested a workaround of hinting the optimizer to use the primary index; e.g. ... FROM users@primary ....

Finally, if you’re up for it, if you could re-run the queries but prefixing them with explain analyze (debug), that would provide additional detail they could work with.

Thanks again for your persistence.

Mike

Hi @mgoddard, thanks again. Is there an issue somewhere I could follow?

Regarding the analyze debug, it generated 2 zip files what is the best way to share this? I had a look and it seems the output might have information I wouldn’t want to expose publicly.

@vtavares , I think it would make sense to continue this discussion via Slack (Join the CockroachDB Slack community | Cockroach Labs), since that way I could provide a secure upload link to you. If you could join up using this link, then DM me (mgoddard), that would be great.

Best,

Mike