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