On Mon, 24 Aug 2009, Gavin Love wrote:
> I seem to be unable to get postgres to use a gist index we have on a circle
> data type.
> 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
If a sequential scan takes 39 ms, and returns 5898 rows, I'd say it's much
quicker than an index scan could ever be. Postgres assumes that a
sequential scan can access disc at a reasonable rate, but an index scan
involves lots of seeking, which can be a lot slower. You would be looking
at 6000 seeks here if the data wasn't in the cache, which could take tens
of seconds.
> 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.
Try with a lot more data, like a thousand times as much. You will probably
find that Postgres will automatically switch over to an index scan when it
becomes beneficial.
Alternatively, if you really want to force its hand (just for testing
purposes), then try running:
SET enable_seqscan TO off;
and see what happens.
Matthew
--
When I first started working with sendmail, I was convinced that the cf
file had been created by someone bashing their head on the keyboard. After
a week, I realised this was, indeed, almost certainly the case.
-- Unknown