[postgis-users] ST_DWithin Performance

[Carl S. Yestrau Jr.]

When I removed the "ORDER BY created_on DESC" the results were almost
instantaneous (which makes sense as it just returns the first 'x'
result defined by LIMIT). I don't have access to the machine at the
moment, but can get the plan for that if it's helpful.

I'm pretty confused why things are so slow. The only thing I can think
of is possibly the data density, I have over 700K records within a 20
minute window.

On Sat, Feb 12, 2011 at 12:27 AM, strk <strk at keybit.net> wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>