Message-ID: <4F50C269.2020807@thl.fi>
Date: Fri, 2 Mar 2012 14:51:53 +0200
From: =?UTF-8?B?QW5zc2kgS8Okw6RyacOkaW5lbg==?= <anssi.kaariainen@thl.fi>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.27) Gecko/20120216 Lightning/1.0b2 Thunderbird/3.1.19
MIME-Version: 1.0
To: Marti Raudsepp <marti@juffo.org>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Large insert and delete batches
References: <4F4DFBDF.5050901@thl.fi>
	<BC19EF15D84DC143A22D6A8F2590F0A78A9846AA67@EXMAIL.stakes.fi>
	<CABRT9RAiNkwgbOU2hufF4megQWaZKXEqXmAcyi-+bWK=0y+NTw@mail.gmail.com>
In-Reply-To: 
 <CABRT9RAiNkwgbOU2hufF4megQWaZKXEqXmAcyi-+bWK=0y+NTw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: 7bit

On 03/01/2012 10:51 PM, Marti Raudsepp wrote:
> The problem with IN() and ARRAY[] is that the whole list of numbers
> has to be parsed by the SQL syntax parser, which has significant
> memory and CPU overhead (it has to accept arbitrary expressions in the
> list). But there's a shortcut around the parser: you can pass in the
> list as an array literal string, e.g:
> select * from the_table where id = ANY('{1,2,3,4,5}')
OK, that explains the memory usage.
> The SQL parser considers the value one long string and passes it to
> the array input function, which is a much simpler routine. This should
> scale up much better.
>
> Even better if you could pass in the array as a query parameter, so
> the SQL parser doesn't even see the long string -- but I think you
> have to jump through some hoops to do that in psycopg2.
Luckily there is no need to do any tricks. The question I was trying to 
seek answer for was should there be some default batch size for inserts 
and deletes in Django, and the answer seems clear: the problems appear 
only when the batch sizes are enormous, so there doesn't seem to be a 
reason to have default limits. Actually, the batch sizes are so large 
that it is likely the Python process will OOM before you can trigger 
problems in the DB.

  - Anssi