PostgreSQL/PostGIS KNN query using <-> operator in ORDER BY clause not using index

Question

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...

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.

Answer 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 ...