Re: Slow query with a lot of data

On Fri, Aug 22, 2008 at 2:31 AM, Moritz Onken <onken(at)houseofdesign(dot)de> wrote:
> Am 21.08.2008 um 19:08 schrieb Merlin Moncure:
>
>> On Thu, Aug 21, 2008 at 11:07 AM, Moritz Onken <onken(at)houseofdesign(dot)de>
>> wrote:
>>>
>>> Am 21.08.2008 um 16:39 schrieb Scott Carey:
>>>
>>>> It looks to me like the work_mem did have an effect.
>>>>
>>>> Your earlier queries had a sort followed by group aggregate at the top,
>>>> and now its a hash-aggregate. So the query plan DID change. That is
>>>> likely
>>>> where the first 10x performance gain came from.
>>>
>>> But it didn't change as I added the sub select.
>>> Thank you guys very much. The speed is now ok and I hope I can finish
>>> tihs
>>> work soon.
>>>
>>> But there is another problem. If I run this query without the limitation
>>> of
>>> the user id, postgres consumes about 150GB of disk space and dies with
>>>
>>> ERROR: could not write block 25305351 of temporary file: No space left
>>> on
>>> device
>>>
>>> After that the avaiable disk space is back to normal.
>>>
>>> Is this normal? The resulting table (setup1) is not bigger than 1.5 GB.
>>
>> Maybe the result is too big. if you explain the query, you should get
>> an estimate of rows returned. If this is the case, you need to
>> rethink your query or do something like a cursor to browse the result.
>>
>> merlin
>
> There will be a few million rows. But I don't understand why these rows
> bloat up so much. If the query is done the new table is about 1 GB in size.
> But while the query is running it uses >150GB of disk space.

can we see explain?

merlin