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 the real 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 our control) ?
Regards
signature.asc
Description: OpenPGP digital signature
