Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
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 at 1:21
    
postgresql.org/docs/current/static/indexes-types.html suggests that <@> isn't GiST-indexable. –  Craig Ringer Feb 28 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 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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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