From: Anssi Kääriäinen <anssi.kaariainen@thl.fi>
To: Marti Raudsepp <marti@juffo.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Large insert and delete batches
Date: Fri, 2 Mar 2012 14:51:53 +0200
Message-ID: <4F50C269.2020807@thl.fi> (raw)
In-Reply-To: <CABRT9RAiNkwgbOU2hufF4megQWaZKXEqXmAcyi-+bWK=0y+NTw@mail.gmail.com>
References: <4F4DFBDF.5050901@thl.fi>
<BC19EF15D84DC143A22D6A8F2590F0A78A9846AA67@EXMAIL.stakes.fi>
<CABRT9RAiNkwgbOU2hufF4megQWaZKXEqXmAcyi-+bWK=0y+NTw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: anssi.kaariainen@thl.fi, marti@juffo.org
Subject: Re: Large insert and delete batches
In-Reply-To: <4F50C269.2020807@thl.fi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox