| From: | Gavin Love <gavin(at)splicer(dot)org(dot)uk> | 
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org | 
| Subject: | Indexing on a circle datatype | 
| Date: | 2009-08-24 16:27:49 | 
| Message-ID: | 4A92BF85.7070507@splicer.org.uk | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-performance | 
Hey,
I seem to be unable to get postgres to use a gist index we have on a 
circle data type.
Table "public.tradesmen_profiles"
       Column         |            Type             |       Modifiers    
-----------------------+-----------------------------+----------------------- 
id                          | integer                     | not null
work_area             | circle                       |
Indexes:
   "tradesmen_profiles_pkey" PRIMARY KEY, btree (id)
    "tradesmen_profiles_test" gist (work_area)
We are then trying to do the following query
SELECT  id FROM  tradesmen_profiles WHERE tradesmen_profiles.work_area  
@> point(0.0548691728419,51.5404384172);
Which produces the following:
QUERY PLAN                                                     
----------------------------------------------------------------------------------------------------------------------- 
Seq Scan on tradesmen_profiles  (cost=0.00..3403.55 rows=14942 width=4) 
(actual time=0.042..31.427 rows=5898 loops=1)
  Filter: (work_area @> '(0.0548691728419,51.5404384172)'::point)
Total runtime: 39.556 ms
I have also vacuum'd and reindexed the table after building the index
VACUUM ANALYZE VERBOSE tradesmen_profiles;
REINDEX TABLE tradesmen_profiles;
So am I just trying to do something that is not possible or have I just 
made a mistake with what I am trying to do?
This is not a big problem just now but as our data set grows I am 
worried that having to do a sequence scan on this table every time will 
be a serious performance overhead.
Thanks for your help,
Gavin
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Matthew Wakeling | 2009-08-24 17:03:48 | Re: Indexing on a circle datatype | 
| Previous Message | Kevin Grittner | 2009-08-24 14:45:42 | Re: improving my query plan |