From: | Moritz Onken <onken(at)houseofdesign(dot)de> |
---|---|
To: | |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Slow query with a lot of data |
Date: | 2008-08-22 06:31:56 |
Message-ID: | 5A4C5EB2-4EC0-4D11-8318-ED154A11E35F@houseofdesign.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
moritz
From | Date | Subject | |
---|---|---|---|
Next Message | Mathias Stjernström | 2008-08-22 06:35:43 | Re: The state of PG replication in 2008/Q2? |
Previous Message | Mathias Stjernström | 2008-08-22 06:29:11 | Re: The state of PG replication in 2008/Q2? |