Re: Indexing on a circle datatype

Tom Lane wrote:
> Gavin Love <gavin(at)splicer(dot)org(dot)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)