[postgis-users] ST_DWithin Performance

[strk]

On Fri, Feb 11, 2011 at 02:27:16PM -0800, Carl S. Yestrau Jr. wrote:

> Table schema:
>                                        Table "public.geobits"
>     Column     |            Type             |
> Modifiers
> ---------------+-----------------------------+------------------------------------------------------
>  id            | integer                     | not null default
> nextval('geobits_id_seq'::regclass)
>  uuid          | uuid                        | not null
>  raw           | text                        | not null
>  search_config | regconfig                   |
>  ip            | inet                        | not null
>  user_agent    | text                        |
>  created_on    | timestamp without time zone | default now()
>  location      | geography(Point,4326)       | not null
>  parent_id     | integer                     |
>  language_code | text                        |
>  group_id      | integer                     |
> Indexes:
>     "geobits_pkey" PRIMARY KEY, btree (id)
>     "geobits_uuid_key" UNIQUE, btree (uuid)
>     "geobits_created_on_index" btree (created_on)
>     "geobits_group_id_index" btree (group_id)
>     "geobits_location_index" gist (location)
>     "geobits_parent_id_index" btree (parent_id)
>     "geobits_search_config_index" gin (to_tsvector(search_config, raw))
> Foreign-key constraints:
>     "geobits_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id)
>     "geobits_language_code_fkey" FOREIGN KEY (language_code)
> REFERENCES languages(code) ON DELETE SET NULL
>     "geobits_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES
> geobits(id) ON DELETE SET NULL
> Referenced by:
>     TABLE "geobits" CONSTRAINT "geobits_parent_id_fkey" FOREIGN KEY
> (parent_id) REFERENCES geobits(id) ON DELETE SET NULL
> 
> 
> Slow Query:
> EXPLAIN ANALYZE SELECT id, created_on FROM geobits WHERE
> ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(180 90)'),
> 1000) ORDER BY created_on DESC LIMIT 10;
> 
> 
>                         QUERY PLAN
> 
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> --------------------------------------------------------------------------
>  Limit  (cost=205352.10..205352.10 rows=1 width=12) (actual
> time=4078.057..4078.090 rows=10 loops=1)
>    ->  Sort  (cost=205352.10..205352.10 rows=1 width=12) (actual
> time=4078.053..4078.064 rows=10 loops=1)
>          Sort Key: created_on
>          Sort Method:  top-N heapsort  Memory: 17kB
>          ->  Seq Scan on geobits  (cost=0.00..205352.08 rows=1
> width=12) (actual time=0.015..3100.471 rows=708661 loops=1)
>                Filter: ((location &&
> '0101000020E610000000000000008066400000000000805640'::geography) AND
> ('0101000020E610000000000000008066400000000000805640'::geography &&
> _st_expand(location, 1000::double precision)) AND
> _st_dwithin(location, '0101000020E6100000000
> 00000008066400000000000805640'::geography, 1000::double precision, true))
>  Total runtime: 4078.127 ms
> (7 rows)

The "geobits_location_index" gist (location) index is not being used,
for some reason. Why do you say ORDER BY is the culprit ? Does
the plan come out differently w/out that ?

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html