title: Postgres - full table scan too slow - index is not being used
body: <p>I have a table in a postgres database with many columns, among which I have:</p>
  
  <pre><code>n_store_object_id     integer,
  n_latitude            decimal,
  n_longitude           decimal
  </code></pre>
  
  <p>The table has about 250,000 rows at present.</p>
  
  <p>I need to find records with non-null store_object_id located within a fixed distance from a given location.  For distance computation, I have the following function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION fn_geo_distance(numeric, numeric, numeric, numeric)
    RETURNS numeric AS
  $BODY$
  declare
      lat1d       ALIAS for $1;
      lon1d       ALIAS for $2;
      lat2d       ALIAS for $3;
      lon2d       ALIAS for $4;
  
      lat1        DECIMAL := lat1d / 57.29577951;
      lon1        DECIMAL := lon1d / 57.29577951;
      lat2        DECIMAL := lat2d / 57.29577951;
      lon2        DECIMAL := lon2d / 57.29577951;
  begin
      return 3963.0 * acos(sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1));
  end;$BODY$
    LANGUAGE plpgsql IMMUTABLE;
  </code></pre>
  
  <p>Now, the query I would require is simple:</p>
  
  <pre><code>select *
    from objects
   where n_store_object_id is not null
     and fn_geo_distance(51.5, 0, n_latitude, n_longitude) &lt;= 20
  </code></pre>
  
  <p>This takes quite a long time - and when I "explain" this query, I can see a full table scan.  Fair enough.  So I create an index on these three columns:</p>
  
  <pre><code>create index idx_object_location on objects(n_store_object_id, n_latitude, n_longitude)
  </code></pre>
  
  <p>I re-run the query above - and it still takes a long time.  "Explaining" it shows that the newly created index is not being used.  Am I missing something?  Why is it not used and how can I force the engine to use it? Oh, and first of all, would this index even help?</p>
  
  <p>Thanks!</p>