Postgresql – Postgres point() index not being used

indexpostgresqlpostgresql-9.3

\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?

Best Answer

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.

Related Question