Re: COPY and indices?

On Tue, Mar 13, 2012 at 12:51 AM, <hamann(dot)w(at)t-online(dot)de> wrote:
>
> Scott Marlowe wrote:
> 2012/3/12 François Beausoleil <francois(at)teksol(dot)info>:
>> Hi all,
>>
>> When using COPY FROM STDIN to stream thousands of rows (20k and more hourly), what happens with indices? Are they updated only once after the operation, or are they updated once per row? Note that I'm not replacing the table's data: I'm appending to what's already there. I suspect batching writes will be faster than writing each individual row using an INSERT statement.
>
> Copy is faster, but if a single row fails (dup key etc) the whole copy fails.
>
> Hi Scott,
>
> my impression: it would be even faster to drop the indices, do the bulk copy,
> and rebuild the indices after the fact.

That depends. If you've got 100M rows already in place and you're
adding 1000 rows, it's likely cheaper to leave the indexes in place.
OTOH, if you've 1M rows already in place and are adding 100M it's
almost certainly cheaper to drop the indexes and recreate them. So
dropping and recreating the indexes isn't necessarily faster,
depending on how big the table already is.