<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">Hi Carl, <br><br>If most of your data is reasonably static (which time series data often isn't) we have used this approach successfully with some 250,000,000 records in a table:<br><br>use Postgres inheritance facility to partition the table in chunks by time, eg: one year's data per partition. If your request is for data from one year, it can pretty much ignore all the other partitions when searching for data, which speeds things up.<br><br>Apply a Postgres clustered index to the timestamp column for each partition.This physically orders the data on disk by the column values, and given timeseries databases are often queried by time, this can significantly improve performance, as each disk read will normally grab sequential data needed by the query, reducing disk reads & seek times required. A clustered index does not need to be applied to the current
year's partition unless really useful, as maintaining it requires the index to be rebuilt after every insert or update.<br><br>An order by/limit query can be significantly faster with data managed in this way. <br><br>HTH,<br><br> Brent Wood<br><br>--- On <b>Sun, 2/13/11, Paul Ramsey <i><pramsey@opengeo.org></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: Paul Ramsey <pramsey@opengeo.org><br>Subject: Re: [postgis-users] ST_DWithin Performance<br>To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><br>Date: Sunday, February 13, 2011, 11:42 AM<br><br><div class="plainMail">How many records get returned when you drop the LIMIT? If you have to<br>sort 700K things into order, I can see that taking a second or two.<br><br>P.<br><br>On Sat, Feb 12, 2011 at 2:16 PM, Carl S. Yestrau Jr.<br><<a ymailto="mailto:carl@featureblend.com"
href="/mc/compose?to=carl@featureblend.com">carl@featureblend.com</a>> wrote:<br>> When I removed the "ORDER BY created_on DESC" the results were almost<br>> instantaneous (which makes sense as it just returns the first 'x'<br>> result defined by LIMIT). I don't have access to the machine at the<br>> moment, but can get the plan for that if it's helpful.<br>><br>> I'm pretty confused why things are so slow. The only thing I can think<br>> of is possibly the data density, I have over 700K records within a 20<br>> minute window.<br>><br>> On Sat, Feb 12, 2011 at 12:27 AM, strk <<a ymailto="mailto:strk@keybit.net" href="/mc/compose?to=strk@keybit.net">strk@keybit.net</a>> wrote:<br>>> On Fri, Feb 11, 2011 at 02:27:16PM -0800, Carl S. Yestrau Jr. wrote:<br>>><br>>>> Table schema:<br>>>>
Table "public.geobits"<br>>>> Column | Type |<br>>>> Modifiers<br>>>> ---------------+-----------------------------+------------------------------------------------------<br>>>> id | integer | not null default<br>>>> nextval('geobits_id_seq'::regclass)<br>>>> uuid | uuid | not null<br>>>> raw | text | not null<br>>>> search_config | regconfig
|<br>>>> ip | inet | not null<br>>>> user_agent | text |<br>>>> created_on | timestamp without time zone | default now()<br>>>> location | geography(Point,4326) | not null<br>>>> parent_id | integer |<br>>>> language_code | text |<br>>>> group_id | integer |<br>>>> Indexes:<br>>>>
"geobits_pkey" PRIMARY KEY, btree (id)<br>>>> "geobits_uuid_key" UNIQUE, btree (uuid)<br>>>> "geobits_created_on_index" btree (created_on)<br>>>> "geobits_group_id_index" btree (group_id)<br>>>> "geobits_location_index" gist (location)<br>>>> "geobits_parent_id_index" btree (parent_id)<br>>>> "geobits_search_config_index" gin (to_tsvector(search_config, raw))<br>>>> Foreign-key constraints:<br>>>> "geobits_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id)<br>>>> "geobits_language_code_fkey" FOREIGN KEY (language_code)<br>>>> REFERENCES languages(code) ON DELETE SET NULL<br>>>> "geobits_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES<br>>>> geobits(id) ON DELETE SET NULL<br>>>> Referenced
by:<br>>>> TABLE "geobits" CONSTRAINT "geobits_parent_id_fkey" FOREIGN KEY<br>>>> (parent_id) REFERENCES geobits(id) ON DELETE SET NULL<br>>>><br>>>><br>>>> Slow Query:<br>>>> EXPLAIN ANALYZE SELECT id, created_on FROM geobits WHERE<br>>>> ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(180 90)'),<br>>>> 1000) ORDER BY created_on DESC LIMIT 10;<br>>>><br>>>><br>>>> QUERY PLAN<br>>>><br>>>> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>>>>
--------------------------------------------------------------------------<br>>>> Limit (cost=205352.10..205352.10 rows=1 width=12) (actual<br>>>> time=4078.057..4078.090 rows=10 loops=1)<br>>>> -> Sort (cost=205352.10..205352.10 rows=1 width=12) (actual<br>>>> time=4078.053..4078.064 rows=10 loops=1)<br>>>> Sort Key: created_on<br>>>> Sort Method: top-N heapsort Memory: 17kB<br>>>> -> Seq Scan on geobits (cost=0.00..205352.08 rows=1<br>>>> width=12) (actual time=0.015..3100.471 rows=708661 loops=1)<br>>>> Filter: ((location &&<br>>>> '0101000020E610000000000000008066400000000000805640'::geography) AND<br>>>>
('0101000020E610000000000000008066400000000000805640'::geography &&<br>>>> _st_expand(location, 1000::double precision)) AND<br>>>> _st_dwithin(location, '0101000020E6100000000<br>>>> 00000008066400000000000805640'::geography, 1000::double precision, true))<br>>>> Total runtime: 4078.127 ms<br>>>> (7 rows)<br>>><br>>> The "geobits_location_index" gist (location) index is not being used,<br>>> for some reason. Why do you say ORDER BY is the culprit ? Does<br>>> the plan come out differently w/out that ?<br>>><br>>> --strk;<br>>><br>>> () Free GIS & Flash consultant/developer<br>>> /\ <a href="http://strk.keybit.net/services.html" target="_blank">http://strk.keybit.net/services.html</a><br>>> _______________________________________________<br>>> postgis-users mailing list<br>>> <a
ymailto="mailto:postgis-users@postgis.refractions.net" href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a ymailto="mailto:postgis-users@postgis.refractions.net" href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@postgis.refractions.net"
href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></table>