Search Postgresql Archives

median query causes disk to fill up

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

 



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 from
http://book.itzero.com/read/others/0602/OReilly.SQL.Cookbook.Dec.2005_html/0596009763/sqlckbk-CHP-7-SECT-10.html

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