Indexing on a circle datatype - Mailing list pgsql-performance

From Gavin Love
Subject Indexing on a circle datatype
Date
Msg-id 4A92BF85.7070507@splicer.org.uk
Whole thread Raw
Responses Re: Indexing on a circle datatype  (Matthew Wakeling <matthew@flymine.org>)
Re: Indexing on a circle datatype  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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 by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: improving my query plan
Next
From: Matthew Wakeling
Date:
Subject: Re: Indexing on a circle datatype