Sorry, I forgot to also mention that I am running this on a machine with 80GB free disk space, and 1GB RAM (but I wouldn't think that this would be the problem, would it?) --Richard On Apr 13, 9:25 am, "richy...@xxxxxxxxx" <richy...@xxxxxxxxx> wrote: > Hi, > > I'm trying to run a query to find the median value, organized by > date. However, when I run the query, it runs for about 4 hours, and > then quits with the following message: > > > ERROR: could not write block 10447102 of temporary file: No space left on device > > HINT: Perhaps out of disk space? > > My table has 512327 rows and is the following: > > > Table "public.m_uop_times" > > Column | Type | Modifiers > > ----------------+-----------------------------+----------- > > objectid | integer | > > date_part | double precision | > > date_start | timestamp without time zone | > > date_processed | timestamp without time zone | > > gen_time | integer | > > Indexes: > > "m_uop_date_idx" btree (date_processed) > > "m_uop_epoch_idx" btree (date_part) > > "m_uop_gen_idx" btree (gen_time) > > "m_uop_objectid_idx" btree (objectid) > > "m_uop_start_idx" btree (date_start) > > The date_part column is actually simply "EXTRACT (EPOCH FROM > date_start::date)" so that I could put an index on that date, and the > gen_time column is actually "date_processed-date_start" so that there > could be an index on that difference as well. > > My median query is copied fromhttp://book.itzero.com/read/others/0602/OReilly.SQL.Cookbook.Dec.2005... > > Here it is: > > > select date_start, avg(gen_time) > > from ( > > select a.date_start::date, a.gen_time > > from m_uop_times a, m_uop_times b > > where a.date_part = b.date_part > > group by a.date_start::date, a.gen_time > > having sum(case when a.gen_time=b.gen_time then 1 else 0 end) > > >= abs(sum(sign(a.gen_time - b.gen_time))) > > ) as foo > > group by date_start; > > Basically, I want to find the median gen_time for each day. > > Would anyone know a better way to do this, or have suggestions on how > I can make this work without dying? > > Any help appreciated! > --Richard
