Re: Out of memory error when doing an update with IN clause

Tom,

As you can see I had to reduce the number of arguments in the IN clause
to even get the explain.

explain update f_commerce_impressions set servlet_key = 60 where
servlet_key in (68,69,70,71,87,90,94);

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_commerce_impressions_servlet,
idx_commerce_impressions_servlet, idx_commerce_impressions_servlet,
idx_commerce_impressions_servlet, idx_commerce_impressions_servlet,
idx_commerce_impressions_servlet, idx_commerce_impressions_servlet on
f_commerce_impressions (cost=0.00..1996704.34 rows=62287970 width=59)
Index Cond: ((servlet_key = 68) OR (servlet_key = 69) OR (servlet_key
= 70) OR (servlet_key = 71) OR (servlet_key = 87) OR (servlet_key = 90)
OR (servlet_key = 94))
(2 rows)

Tom Lane wrote:

>Sean Shanny <shannyconsulting(at)earthlink(dot)net> writes:
>
>
>>There are no FK's or triggers on this or any of the tables in our
>>warehouse schema. Also I should have mentioned that this update will
>>produce 0 rows as these values do not exist in this table.
>>
>>
>
>Hm, that makes no sense at all ...
>
>
>
>>Here is output from the /usr/local/pgsql/data/servlerlog when this fails:
>>...
>>DynaHashTable: 534773784 total in 65 blocks; 31488 free (255 chunks);
>>534742296 used
>>
>>
>
>Okay, so here's the problem: this hash table has expanded to 500+Mb which
>is enough to overflow your ulimit setting. Some digging in the source
>code shows only two candidates for such a hash table: a tuple hash table
>used for grouping/aggregating, which doesn't seem likely for this query,
>or a tuple-pointer hash table used for detecting already-visited tuples
>in a multiple index scan.
>
>Could we see the EXPLAIN output (no ANALYZE, since it would fail) for
>the problem query? That should tell us which of these possibilities
>it is.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>
>