Обсуждение: slow get_actual_variable_range with long running transactions

Hi,

We're having an issue with planner performance when doing large deletes at the same time as we have long running
transactions,from what we gathered, because of the scan to find the actual minimum and maximum values of the table. 

Instead of trying to explain what happens, here is a very simple example:

(Our load is very similar to this scenario)

The "test" table has one column with an index on it.

Session 1:
=# insert into test select generate_series(1,10000000);

Session 2: do the long running transaction:
=# begin;
=# do_whatever_to_get_a_long_running_transaction

Session 1:
=# delete from test where a>1000000;
=# analyze test;
=# explain select * from test where a > 11000000;
                              QUERY PLAN
----------------------------------------------------------------------
 Index Only Scan using idxa on test  (cost=0.42..4.44 rows=1 width=4)
   Index Cond: (a > 11000000)
(2 rows)

Time: 2606,068 ms (00:02,606)

Of course, what happens here is that the histogram says that max(a) is 1000000, and get_actual_variable_range verifies
thereal upper bound. And has to read quite a few dead index records. 

Is there something to do to avoid the problem (except for the long running transaction, which unfortunately is out of
ourcontrol) ? 

Regards

Marc Cousin <cousinmarc@gmail.com> writes:
> Of course, what happens here is that the histogram says that max(a) is 1000000, and get_actual_variable_range
verifiesthe real upper bound. And has to read quite a few dead index records. 

We've revised that logic several times to reduce the scope of the
problem.  Since you didn't say which PG version you're using exactly,
it's hard to offer any concrete suggestions.  But I fear there's
not a lot you can do about it, other than upgrading if you're on a
pre-v11 release.

            regards, tom lane

Oh, sorry about that, I forgot to detail this. I tested on both 10.13 (which is the production environment on which we
facedthis), and on 12.3, with the same problem. 

On 16/06/2020 17:51, Tom Lane wrote:
> Marc Cousin <cousinmarc@gmail.com> writes:
>> Of course, what happens here is that the histogram says that max(a) is 1000000, and get_actual_variable_range
verifiesthe real upper bound. And has to read quite a few dead index records. 
>
> We've revised that logic several times to reduce the scope of the
> problem.  Since you didn't say which PG version you're using exactly,
> it's hard to offer any concrete suggestions.  But I fear there's
> not a lot you can do about it, other than upgrading if you're on a
> pre-v11 release.
>
>             regards, tom lane
>

On 16/06/2020 18:28, Marc Cousin wrote:
> Oh, sorry about that, I forgot to detail this. I tested on both 10.13 (which is the production environment on which
wefaced this), and on 12.3, with the same problem. 
>
> On 16/06/2020 17:51, Tom Lane wrote:
>> Marc Cousin <cousinmarc@gmail.com> writes:
>>> Of course, what happens here is that the histogram says that max(a) is 1000000, and get_actual_variable_range
verifiesthe real upper bound. And has to read quite a few dead index records. 
>>
>> We've revised that logic several times to reduce the scope of the
>> problem.  Since you didn't say which PG version you're using exactly,
>> it's hard to offer any concrete suggestions.  But I fear there's
>> not a lot you can do about it, other than upgrading if you're on a
>> pre-v11 release.
>>
>>             regards, tom lane
>>
>
As you told me this I did some more tests on PG 12.

The first attempt is the same (maybe some hints being set or something like that), the second is much faster. So
nothingto see here, sorry for the noise. 

And sorry for the previous top posting :)

Regards