Skip site navigation (1) Skip section navigation (2)

Re: [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

From: KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>
To: Bernd Helmle <mailings(at)oopsware(dot)de>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>, pgsql-hackers(at)postgresql(dot)org, Thom Brown <thombrown(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Subject: Re: [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns
Date: 2010-01-25 02:39:21
Message-ID: 4B5D0459.8010308@ak.jp.nec.com (view raw or flat)
Thread:
Lists: pgsql-hackers
(2010/01/25 8:45), KaiGai Kohei wrote:
> (2010/01/25 4:01), Bernd Helmle wrote:
>>
>>
>> --On 24. Januar 2010 19:45:33 +0100 Bernd Helmle<mailings(at)oopsware(dot)de>
>> wrote:
>>
>>> I don't see where this should be related to the number of tables not
>>> part of the inheritance tree (or inheritance at all).
>>
>> To answer that myself: it seems get_attname() introduces the overhead
>> here (forgot about that). Creating additional 16384 tables without any
>> connection to the inheritance increases the times on my Phenom-II Box to
>> round about 2 seconds:
>>
>>
>> Current -HEAD
>>
>> bernd=# ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;
>> ALTER TABLE
>> Time: 409,045 ms
>>
>>
>> With KaiGai's recent patch:
>>
>> bernd=# ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;
>> ALTER TABLE
>> Time: 2402,306 ms
> 
> Hmm....
> 
> Bernd, could you try same test with previous patch?
>    http://archives.postgresql.org/message-id/4B41BB04.2070609@ak.jp.nec.com
> 
> It computes an expected inhcount during find_all_inheritors(), and
> compares it with the target pg_attribute entry?
> 
> I'll also try to measure performance in three cases by myself.
> Please wait for a while...

I set up 11,111 of tables inherited from a common table.

(echo "CREATE TABLE t (a int);"
 for i in `seq 0 9`; do
     echo "CREATE TABLE s$i (b int) INHERITS(t);"
     for j in `seq 0 9`; do
         echo "CREATE TABLE v$i$j (c int) INHERITS(s$i);"
         for k in `seq 0 9`; do
             echo "CREATE TABLE w$i$j$k (d int) INHERITS(v$i$j);"
             for l in `seq 0 9`; do
                 echo "CREATE TABLE x$i$j$k$l (e int) INHERITS(w$i$j$k);"
             done
         done
     done
 done) | psql test

And, I measured time to execute the following query using /usr/bin/time.

  ALTER TABLE t RENAME a TO aa;
  ALTER TABLE t RENAME aa TO aaa;
  ALTER TABLE t RENAME aaa TO aaaa;
  ALTER TABLE t RENAME aaaa TO aaaaa;
  ALTER TABLE t RENAME aaaaa TO a;

The platform is Pentium4 (3.20GHz) and generic SATA drive.

* CVS HEAD - does not care anything
  Avg: 25.840s (25.663s 24.214s 26.958s 26.525s)

* My rev.3 patch - find_all_inheritors_with_inhcount()
  Avg: 26.488s (25.197s 27.847s 25.487s 27.421s)

* My rev.4 patch - find_column_origin(), also fixes AT_AlterColumnType case
  Avg: 28.693s (27.936s 30.295s 29.385s 27.159s)

It seems to me the result is different from Bernd's report.

Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>

In response to

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2010-01-25 02:55:05
Subject: Re: Mammoth in Core?
Previous:From: Takahiro ItagakiDate: 2010-01-25 02:16:23
Subject: Syntax supplements for SET options

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group