Re: Strange nested loop for an INSERT - Mailing list pgsql-performance

From phb07
Subject Re: Strange nested loop for an INSERT
Date
Msg-id 224290ce-84bb-935f-1743-3f036ae3d508@apra.asso.fr
Whole thread Raw
In response to Re: Strange nested loop for an INSERT  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Strange nested loop for an INSERT  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
List pgsql-performance
Thanks, Tom, for this quick answer.


Le 12/09/2016 à 16:41, Tom Lane a écrit :
> phb07 <phb07@apra.asso.fr> writes:
>> The performance issue, encountered in very specific situations, is the
>> time needed to cancel a significant number of insertions.
>> I have build a simple test case that reproduces the problem without the
>> need of the extension. It just mimics the behaviour.
> At least for this example, the problem is that the DELETE enormously
> alters the statistics for the t1_log.tuple column (going from 100% "NEW"
> to 50% "NEW" and 50% "OLD"), but the plan for your last command is
> generated with stats saying there are no "OLD" entries.  So you get a plan
> that would be fast for small numbers of "OLD" entries, but it sucks when
> there are lots of them.  The fix I would recommend is to do a manual
> "ANALYZE t1_log" after such a large data change.  Auto-ANALYZE would fix
> it for you after a minute or so, probably, but if your script doesn't want
> to wait around then an extra ANALYZE is the ticket.
>
>             regards, tom lane
>
I understand the point (and I now realize that I should have found the
answer by myself...)
Adding an ANALYZE of the log table effectively changes the plan and
brings good performances for the INSERT statement.
The drawback is the overhead of this added ANALYZE statement. With a
heavy processing like in this test case, it is worth to be done. But for
common cases, it's a little bit expensive.
But I keep the idea and I will study the best solution to implement.

Regards. Philippe.



pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Strange nested loop for an INSERT
Next
From: Pietro Pugni
Date:
Subject: Disk filled-up issue after a lot of inserts and drop schema