Tom Lane wrote:
Gavin Love <gavin@xxxxxxxxxxxxxx> 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)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
