On Fri, Aug 22, 2008 at 2:31 AM, Moritz Onken <onken@xxxxxxxxxxxxxxxx> wrote: > Am 21.08.2008 um 19:08 schrieb Merlin Moncure: > >> On Thu, Aug 21, 2008 at 11:07 AM, Moritz Onken <onken@xxxxxxxxxxxxxxxx> >> 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
