Indexing on a circle datatype
От | Gavin Love |
---|---|
Тема | Indexing on a circle datatype |
Дата | |
Msg-id | 4A92BF85.7070507@splicer.org.uk обсуждение исходный текст |
Ответы |
Re: Indexing on a circle datatype
Re: Indexing on a circle datatype |
Список | 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
В списке pgsql-performance по дате отправления: