Re: Decreasing performance in table partitioning

Thank you. I was away for a few days.

This is PG version 9.1. Now, this is in a function. As far as I understand, every function is a single transaction. I have not created exception blocks because I don't have any special handling for exceptions. I'm fine with the default.

The data in each table is for about 10 months, so it looks about 10 times each cycle.

What has occured to me, though, is that maybe I should write the DELETE statement as DELETE FROM ONLY, as the previously created partitions would be scanned, despite having no applicable data, the way I wrote it. Does that make sense?

On 07/09/2014, at 19:50, Tom Lane wrote:

> 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

--
חרות מעוז
יוניסל פתרונות סלולריים מתקדמים
☎ 03-5181717 שלוחה 742