Re: median query causes disk to fill up

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(dot)(dot)(dot)(at)gmail(dot)com" <richy(dot)(dot)(dot)(at)gmail(dot)com> 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