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 <[email protected]> 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
