2

I am trying to set up a KNN query using the <-> operator in the ORDER BY clause and the query planner does not appear to be using the spatial index as expected.

I am not using bleeding edge versions of PG and PostGIS here, but I believe they should be sufficient to use the spatial index for this operation. PG version is 11.2 and PostGIS version is 2.5.

This example is on EPSG:4326 which would not be ideal for KNN obviously but this is for testing -- I will project or use geographies when I can get the index successfully working.

I have created a spatial index on the geometry of a administrative boundaries dataset:

create index idx_gadm_subdivided_geoms_100_geom on psm_grids.gadm_subdivided_geoms_100 using gist(geom);
vacuum analyze psm_grids.gadm_subdivided_geoms_100;

Then, when using the simplest possible example of using the <-> point to find KNN, the query planner is not using the spatial index:

Query:

explain SELECT name_0 as country  FROM psm_grids.gadm_subdivided_geoms_100
      ORDER BY geom <-> 'SRID=4326;POINT (0.75 76)' limit 1;

Here is the Query plan generated:

+---------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                    |
|---------------------------------------------------------------------------------------------------------------|
| Limit  (cost=323842.61..323842.73 rows=1 width=17)                                                            |
|   ->  Gather Merge  (cost=323842.61..526171.21 rows=1734124 width=17)                                         |
|         Workers Planned: 2                                                                                    |
|         ->  Sort  (cost=322842.59..325010.25 rows=867062 width=17)                                            |
|               Sort Key: ((geom <-> '0101000020E6100000000000000000E83F0000000000005340'::geometry))           |
|               ->  Parallel Seq Scan on gadm_subdivided_geoms_100  (cost=0.00..318507.28 rows=867062 width=17) |
+---------------------------------------------------------------------------------------------------------------+

As I understand the documentation, this operation should be using the spatial index. Anyone know what I am doing wrong?

EDIT

Including some extra information as requested.

Count of my administrative boundary dataset:

select count(*) from psm_grids.gadm_subdivided_geoms_100;
+---------+
| count   |
|---------|
| 2080296 |
+---------+
SELECT 1

Including the explain analyze statement:

explain analyze SELECT name_0 as country  FROM psm_grids.gadm_subdivided_geoms_100
       ORDER BY geom <-> 'SRID=4326;POINT (0.75 76)' limit 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                      |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit  (cost=323842.61..323842.73 rows=1 width=17) (actual time=4573.494..4589.397 rows=1 loops=1)                                                              |
|   ->  Gather Merge  (cost=323842.61..526171.21 rows=1734124 width=17) (actual time=4573.491..4589.391 rows=1 loops=1)                                           |
|         Workers Planned: 2                                                                                                                                      |
|         Workers Launched: 2                                                                                                                                     |
|         ->  Sort  (cost=322842.59..325010.25 rows=867062 width=17) (actual time=4547.376..4547.376 rows=1 loops=3)                                              |
|               Sort Key: ((geom <-> '0101000020E6100000000000000000E83F0000000000005340'::geometry))                                                             |
|               Sort Method: top-N heapsort  Memory: 25kB                                                                                                         |
|               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB                                                                                              |
|               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB                                                                                              |
|               ->  Parallel Seq Scan on gadm_subdivided_geoms_100  (cost=0.00..318507.28 rows=867062 width=17) (actual time=0.081..4440.016 rows=693432 loops=3) |
| Planning Time: 3.085 ms                                                                                                                                         |
| Execution Time: 4589.480 ms                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 4.776s (4 seconds), executed in: 4.753s (4 seconds)

EDIT 2

The index is definitely there...

enter image description here

However it doesn't kick in on a st_dwithin query either:

explain analyze SELECT name_0 as country  FROM psm_grids.gadm_subdivided_geoms_100
       where st_dwithin(geom, 'SRID=4326;POINT (0.75 76)', 1);
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
| QUERY PLAN                                                                                                                                                                                               >
|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
| Gather  (cost=1000.00..538066.18 rows=1 width=9) (actual time=1792.562..1794.603 rows=0 loops=1)                                                                                                         >
|   Workers Planned: 2                                                                                                                                                                                     >
|   Workers Launched: 2                                                                                                                                                                                    >
|   ->  Parallel Seq Scan on gadm_subdivided_geoms_100  (cost=0.00..537066.08 rows=1 width=9) (actual time=1767.582..1767.582 rows=0 loops=3)                                                              >
|         Filter: ((geom && '0103000020E61000000100000005000000000000000000D0BF0000000000C05240000000000000D0BF0000000000405340000000000000FC3F0000000000405340000000000000FC3F0000000000C0524000000000000'>
|         Rows Removed by Filter: 693432                                                                                                                                                                   >
| Planning Time: 0.205 ms                                                                                                                                                                                  >
| Execution Time: 1794.623 ms                                                                                                                                                                              >
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
EXPLAIN
Time: 1.874s (a second), executed in: 1.857s (a second)

So actually -- The problem appears to be that the spatial index of this table just isn't being used.

4
  • What's the actual table row count? PG won't load the index for sufficiently small tables (a few thousand rows), where a direct seq scan can be a lot more efficient. Check (and post) the EXPLAIN ANALYZE output, too.
    – geozelot
    Mar 7, 2021 at 12:35
  • @geozelot thank you for your response, added the additional information you requested as an edit to the OP. Seems to me the table should be well large enough to warrant the use of the spatial index? Mar 8, 2021 at 19:46
  • It should, and it is indeed odd that PG doesn't fire up the index. Can you verify it is actually created (forgot to COMMIT or sth.?). Can you run a ST_DWithin and/or <#> query on the same point to check if the index kicks in?
    – geozelot
    Mar 9, 2021 at 11:09
  • @geozelot -- the index is there but just isn't being used (see latest edits). Appears to be that this is a more general problem with the index in the table rather than a problem with this particular KNN construction... Thanks for your help though. Mar 9, 2021 at 22:10

1 Answer 1

1

For some reason, PG sees the index as being unusable, or cannot use it; some debugging suggestions:

  • try the obvious:
    REINDEX INDEX idx_gadm_subdivided_geoms_100_geom; and then
    VACUUM ANALYZE psm_grids.gadm_subdivided_geoms_100;
    or DROP INDEX and recreate
  • if the REINDEX takes an unusual amount of time, check if it gets blocked
    SELECT  pid, 
            usename,
            application_name,
            pg_blocking_pids(pid) AS blocked_by, 
            query AS blocked_query,
            state
    FROM    pg_stat_activity
    --WHERE   CARDINALITY(pg_blocking_pids(pid)) > 0
    ORDER BY
            pid
    ;
    
    Find and close any connections having a PID that appears in the blocked_by column (last resort if you cannot cancel or close the connection elsewhere: SELECT pg_terminate_backend(<PID_from_above_query>);)
  • although your screenshot shows results of a similar query, check the index status explicitly:
    SELECT indexrelid::REGCLASS AS index_name, *
    FROM   pg_index
    WHERE  indrelid::REGCLASS = 'gadm_subdivided_geoms_100'::REGCLASS
    ;
    
    and see if indisvalid = TRUE
  • make sure there is no idle in transaction connection open; even unrelated queries can render access to (newly created) resources impossible:
    SELECT  datname,
            pid,
            usename,
            client_addr,
            client_port,
            xact_start,
            backend_start,
            query_start,
            AGE(CLOCK_TIMESTAMP(), query_start) AS "age",
            state,
            query
    FROM    pg_stat_activity
    --WHERE   state LIKE 'idle%'
    ORDER BY
            query_start ASC
    ;
    
    Find and close any connections having idle in transaction as state (last resort if you cannot cancel or close the connection elsewhere: SELECT pg_terminate_backend(<PID_from_above_query>);)
  • alternatively, check if any pending locks are requested:
    SELECT  relation::REGCLASS,
            pid,
            "mode"
    FROM    pg_locks
    WHERE   NOT "granted"
    ;
    
    and compare to the idle queries from above; cancel those running for any unreasonable long time (note that system maintenance and statistics queries should take less than a second, except the auto-vacuum daemon...)

On another note:

The (K)NN query is absolutely correct. Instead of a projection, consider using a functional index

CREATE INDEX... USING GIST ((geom::GEOGRAPHY));

and then

... ORDER BY geom::GEOGRAPHY <-> 'SRID=4326;POINT (0.75 76)'::GEOGRAPHY ...

1
  • I don't know what the problem was, but remaking the index solved it. And now the full KNN in lateral join query is working with the functional index! I like working with geographies but I usually make another geog column, I didn't think that was going to work with a geography column. I didn't know about the functional index construction. Thanks so much for the help! Mar 10, 2021 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.