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

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


WHERE point(53.574753, -2.1) <@> point(latitude, longitude) < 100;


 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?

2 Answers 2


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.

  • I've just seeded the database with 100,000 rows, yet still it is not using the index. If I set enable_seqscan to off, the explain query still says "Seq Scan on aggregate"?? random_page_cost is set to 4.
    – StuR
    Feb 28, 2014 at 1:21
  • 1
    postgresql.org/docs/current/static/indexes-types.html suggests that <@> isn't GiST-indexable. Feb 28, 2014 at 1:24
  • Ah yes, it seems that you can't use indexes with point-based searches, so I'll have to use cubes instead.
    – StuR
    Feb 28, 2014 at 1:40

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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