Postgres point() index not being used

Question

\d aggregate

              Materialized view "public.aggregate"
          Column      |            Type             | Modifiers 
    ------------------+-----------------------------+-----------
     id               | integer                     | 
     searchable       | text                        | 
     name             | character varying(255)      | 
     source_type      | character varying(255)      | 
     source_id        | integer                     | 
     latitude         | double precision            | 
     longitude        | double precision            | 
     created_at       | timestamp without time zone | 
     updated_at       | timestamp without time zone | 

Indexes:
    "aggregate_lat_lng_point" gist (point(latitude, longitude))
    "searchable_tsvector" gin (to_tsvector('english'::regconfig, COALESCE(searchable, ''::text)))

Query:

EXPLAIN ANALYZE SELECT name FROM aggregate 
WHERE point(53.574753, -2.1) <@> point(latitude, longitude) < 100;

Result:

 Seq Scan on aggregate_mv  (cost=0.00..23.01 rows=172 width=516) (actual time=0.019..0.522 rows=458 loops=1)
   Filter: (('(53.574753,-2.1)'::point <@> point(latitude, longitude)) < 100::double precision)
   Rows Removed by Filter: 320
 Total runtime: 0.579 ms
(4 rows)

As you can see, the index is not being used. The table contains 525 rows, and the above query returns 195 rows. The table is a materialized view, but that shouldn't make any difference, I have other indexes which work fine. Any ideas as to why my index is not being used in the above query?

Answer 1

The index isn't used because the table is tiny. It'll probably be faster to scan it than use the index.

To confirm, set (for testing only) SET enable_seqscan = off.

If it is in fact faster to use an index here, perhaps you need to lower random_page_cost.

Update: doesn't look like <@> is listed in the supported operators for GiST, so unless there's an extension that adds an appropriate opclass, you're probably out of luck.

Answer 2

I switched to use cube based to take advantage of indexing:

CREATE INDEX aggregate_cube ON aggregate USING gist (ll_to_earth(latitude, longitude));

explain analyze select name from aggregate where earth_box(ll_to_earth(53.574753, -2.1), 100 * 1609.344) @> ll_to_earth(latitude, longitude);

 Bitmap Heap Scan on aggregate  (cost=9.06..259.53 rows=69 width=516) (actual time=0.218..0.476 rows=134 loops=1)
   Recheck Cond: ('(3779643.6387679, -143776.582140441, 5127079.5615671),(3789643.63851185, -133776.582396498, 5137079.56131104)'::cube @> (ll_to_earth(latitude, longitude))::cube)
   ->  Bitmap Index Scan on aggregate_cube  (cost=0.00..9.05 rows=69 width=0) (actual time=0.190..0.190 rows=134 loops=1)
         Index Cond: ('(3779643.6387679, -143776.582140441, 5127079.5615671),(3789643.63851185, -133776.582396498, 5137079.56131104)'::cube @> (ll_to_earth(latitude, longitude))::cube)
 Total runtime: 0.516 ms
(5 rows)