Re: Indexing on a circle datatype

Tom Lane wrote:
> Gavin Love <gavin@splicer.org.uk> writes:
>> I seem to be unable to get postgres to use a gist index we have on a
>> circle data type.
>> SELECT  id FROM  tradesmen_profiles WHERE tradesmen_profiles.work_area
>> @> point(0.0548691728419,51.5404384172);
>
> So far as I can see, the member operators of gist circle_ops are
>
>  gist         | circle_ops         | <<(circle,circle)
>  gist         | circle_ops         | &<(circle,circle)
>  gist         | circle_ops         | &>(circle,circle)
>  gist         | circle_ops         | >>(circle,circle)
>  gist         | circle_ops         | <@(circle,circle)
>  gist         | circle_ops         | @>(circle,circle)
>  gist         | circle_ops         | ~=(circle,circle)
>  gist         | circle_ops         | &&(circle,circle)
>  gist         | circle_ops         | |>>(circle,circle)
>  gist         | circle_ops         | <<|(circle,circle)
>  gist         | circle_ops         | &<|(circle,circle)
>  gist         | circle_ops         | |&>(circle,circle)
>  gist         | circle_ops         | @(circle,circle)
>  gist         | circle_ops         | ~(circle,circle)
>
> (this is extracted from the output of the query shown in 8.4 docs
> section 11.9).  So, circle @> point is out of luck.  Try using a
> zero- or small-radius circle on the right.
>

I thought that might be the case but was unsure from the documentation I
could find. With a small circle it does indeed use the index.

Thanks for your help.

EXPLAIN ANALYZE
SELECT  tradesmen_profiles.id FROM  tradesmen_profiles WHERE
tradesmen_profiles.work_area  @> circle
'((0.0548691728419,51.5404384172),0)';

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on tradesmen_profiles  (cost=4.50..115.92 rows=30
width=4) (actual time=2.339..18.495 rows=5898 loops=1)
    Filter: (work_area @> '<(0.0548691728419,51.5404384172),0>'::circle)
    ->  Bitmap Index Scan on tradesmen_profiles_test  (cost=0.00..4.49
rows=30 width=0) (actual time=1.927..1.927 rows=6404 loops=1)
          Index Cond: (work_area @>
'<(0.0548691728419,51.5404384172),0>'::circle)
  Total runtime: 26.554 ms
(5 rows)