[postgis-users] ST_DWithin Performance

[Carl S. Yestrau Jr.]

Thanks Mark,
Here's the additional information, the ORDER BY created_on DESC seems
to be the issue even though it's indexed.

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)

On Fri, Feb 11, 2011 at 3:42 AM, Mark Cave-Ayland
<mark.cave-ayland at siriusit.co.uk> wrote:
> On 10/02/11 23:37, Carl S. Yestrau Jr. wrote:
>
>> I have a geography(Point,4326) column with a gist index with ~700K
>> records. A simple query using the ST_DWithin() is taking about 15
>> secs. Any pointers from the resident experts?
>>
>> Arch Linux x86
>> postgis-1.5.2-1
>> postgresql-9.0.2-2
>
> In order to help, we'd need a lot more information than this - in particular
> the schema of the tables (\d in psql output) and the EXPLAIN ANALYZE of the
> slow query would be the bare minimum information you'd need to provide.
>
>
> ATB,
>
> Mark.
>
> --
> Mark Cave-Ayland - Senior Technical Architect
> PostgreSQL - PostGIS
> Sirius Corporation plc - control through freedom
> http://www.siriusit.co.uk
> t: +44 870 608 0063
>
> Sirius Labs: http://www.siriusit.co.uk/labs
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>