Re: Decreasing performance in table partitioning

Herouth Maoz <herouth(at)unicell(dot)co(dot)il> writes:
> My problem is the main loop, in which data for one month is moved from the old table to the partition table.

> EXECUTE FORMAT (
> 'WITH del AS (
> DELETE FROM %1$I.%2$I
> WHERE %3$I >= %4$L AND %3$I < %5$L
> RETURNING *
> )
> INSERT INTO %6$I.%7$I
> SELECT * FROM del',
> p_main_schema,
> p_table_name,
> p_date_field_name,
> v_curr_month_str,
> v_curr_month_to_str,
> p_partition_schema,
> v_partition_name
> );

> In the first few iterations, this runs in very good times. But as
> iterations progress, performance drops, despite the size of the date for
> each month being more or less the same.

How many of these are you doing in a single transaction? Are you doing
them in separate exception blocks? What PG version is this exactly?

My guess is that the cycles are going into finding out that tuples deleted
by a prior command are in fact dead to the current command (though still
live to outside observers, so they can't be hinted as dead). That ought
to be relatively cheap if it's all one subtransaction, but if there were a
large number of separate subtransactions involved, maybe not so much.

regards, tom lane