[postgis-users] ST_DWithin Performance

[Paul Ramsey]

How many records get returned when you drop the LIMIT? If you have to
sort 700K things into order, I can see that taking a second or two.

P.

On Sat, Feb 12, 2011 at 2:16 PM, Carl S. Yestrau Jr.
<carl at featureblend.com> wrote:
> 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
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>