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.

• StuR: 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.
• Craig Ringer: postgresql.org/docs/current/static/indexes-types.html suggests that <@> isn't GiST-indexable.
• StuR: Ah yes, it seems that you can't use indexes with point-based searches, so I'll have to use cubes instead.

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)