Re: PATCH: optimized DROP of multiple tables within a transaction

Dne 10.12.2012 16:38, Andres Freund napsal:
> On 2012-12-08 17:07:38 +0100, Tomas Vondra wrote:
>> I've done some test and yes - once there are other objects the
>> optimization falls short. For example for tables with one index, it
>> looks like this:
>>
>> 1) unpatched
>>
>> one by one: 28.9 s
>> 100 batches: 23.9 s
>>
>> 2) patched
>>
>> one by one: 44.1 s
>> 100 batches: 4.7 s
>>
>> So the patched code is by about 50% slower, but this difference
>> quickly
>> disappears with the number of indexes / toast tables etc.
>>
>> I see this as an argument AGAINST such special-case optimization. My
>> reasoning is this:
>>
>> * This difference is significant only if you're dropping a table
>> with
>> low number of indexes / toast tables. In reality this is not going
>> to
>> be very frequent.
>>
>> * If you're dropping a single table, it really does not matter - the
>> difference will be like 100 ms vs. 200 ms or something like that.
>
> I don't particularly buy that argument. There are good reasons (like
> avoiding deadlocks, long transactions) to drop multiple tables
> in individual transactions.
> Not that I have a good plan to how to work around that though :(

Yeah, if you need to drop the tables one by one for some reason, you
can't get rid of the overhead this way :-(

OTOH in the example above the overhead is ~50%, i.e. 1.5ms / table with
a
single index. Each such associated relation (index, TOAST table, ...)
means
a relation that needs to be dropped and on my machine, once I reach ~5
relations there's almost no difference as the overhead is balanced by
the
gains.

Not sure how to fix that in an elegant way, though :-(

Tomas