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
