Re: swap storm created by 8.2.3

Richard Huxton wrote:
> Joseph Shraibman wrote:
>>>> I ran a query that was "SELECT field, count(*) INTO TEMP temptable"
>>>> and it grew to be 10gig (as reported by top)
>>>
>>> What was the real query?
>>
>> First I selected 90634 rows (3 ints) into the first temp table, then I
>> did "select intfield1, count(intfield2) FROM realtable rt WHERE rt.id
>> = temptable.id and other conditions on rt here GROUP BY intfield1".
>> The size of the second temp table should have been no more than 60000
>> rows.
>
<SNIP>
>
> If your memory settings in postgresql.conf are reasonable (and they look
> fine), this shouldn't happen. Let's see if an EXPLAIN sheds any light.
>
=> explain SELECT ml.uid, count(ml.jid) AS cnt INTO TEMP tempml FROM  ml
WHERE ml.jid = tempjr1180108653561.id  AND ml.status IN(2,5,20) GROUP BY
ml.uid;
NOTICE:  adding missing FROM-clause entry for table "tempjr1180108653561"
LINE 2: ...INTO TEMP tempml FROM ml WHERE ml.jid = tempjr1180...
                                                              ^
                                         QUERY PLAN
------------------------------------------------------------------------------------------
  HashAggregate  (cost=11960837.72..11967601.06 rows=541067 width=8)
    ->  Hash Join  (cost=9675074.94..11849780.55 rows=22211434 width=8)
          Hash Cond: (tempjr1180108653561.id = ml.jid)
          ->  Seq Scan on tempjr1180108653561  (cost=0.00..31.40
rows=2140 width=4)
          ->  Hash  (cost=6511767.18..6511767.18 rows=181979021 width=8)
                ->  Seq Scan on ml  (cost=0.00..6511767.18
rows=181979021 width=8)
                      Filter: (status = ANY ('{2,5,20}'::integer[]))
(7 rows)