[postgis-users] Query not using index - why not?

[Mark Cave-Ayland]

> <QUERY>
> 
> EXPLAIN SELECT *, DISTANCE_SPHEROID('SRID=4278;POINT(120.4 
> 14.583333)', longlat, 
> 'SPHEROID[\"Airy_1830\",6377563.396,299.3249646]') AS 
> distance FROM location WHERE 
> DISTANCE_SPHEROID('SRID=4278;POINT(120.4 14.583333)', 
> longlat, 'SPHEROID[\"Airy_1830\",6377563.396,299.3249646]') < 50000;
> 
>                                                               
>                   QUERY PLAN                                  
>                                                
> --------------------------------------------------------------
> --------------------------------------------------------------
> -----------------------------------------------
>  Seq Scan on "location"  (cost=0.00..25.00 rows=333 width=40)
>    Filter: (distance_spheroid('SRID=4278;POINT(120.4 
> 14.583333)'::geometry, longlat, 
> 'SPHEROID("Airy_1830",6377563.396,299.3249646)'::spheroid) < 
> 50000::double precision) (2 rows)
> 
> </QUERY>


Hmmm... the estimate of 333 rows in your table looks quite small. How
many rows does your table have? If it is a lot more then you need to do
a VACUUM ANALYZE to allow postgres to correctly determine which indexes
should be used when accessing the table.


Hope this helps,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.