Search Postgresql Archives

Re: median query causes disk to fill up

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux