Re: median query causes disk to fill up

richyen3(at)gmail(dot)com wrote:
> 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?)

Running out of disk space during a select sounds like you ran out of
memory and out of swap consequently. Maybe other applications on your
machine are using up a lot, or maybe you didn't configure enough memory
for postgres?

As for finding a median, if memory serves me correctly that's the center
value of a range, right? I'm not sure which value to pick if there's an
even number of results, but something like the following sounds a
logical approach to me:

SELECT a.value
FROM table a, table b
GROUP BY a.value
HAVING COUNT(a.value <= b.value) = COUNT(a.value >= b.value)

I suppose the AVG() is to solve the case where there are two candidate
values for a median. I don't know how fast this is, or whether this even
yields a correct answer, it's just an idea.
It probably makes sense to add ordering to both result sets, but I'm
kind of hoping the planner can handle that.

Having an ordered index on those values would really help a lot of
course, but IIRC that's not yet possible...

And now I ran out of the limited amount of time I can spend on stuff
like this :P

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //