Re: Altering a column type - Most efficient way - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Altering a column type - Most efficient way
Date
Msg-id 48772E39.EE98.0025.0@wicourts.gov
Whole thread Raw
In response to Re: Altering a column type - Most efficient way  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses [SOLVED] Re: Altering a column type - Most efficient way
List pgsql-performance
>>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Ow Mun Heng wrote:
>>> If it were this simple a change, I'm not certain why (I believe) PG
is
>>> checking each and every row to see if it will fit into the new
column
>>> definition/type.
>
>> Because the code that does the ALTER TYPE is very generic, and it
>> doesn't (yet) have an optimization that tells it to skip the check
and
>> the possible table rewrite in the cases where it's obviously not
needed
>> (like this one).
>
> Awhile back I looked into teaching ALTER TYPE that it needn't
rewrite
> if the type conversion expression parses out as just a Var with
> RelabelType, but it seemed that that wouldn't cover very much of the
> use-cases where a human thinks that it's "obvious" that no rewrite
> is needed.

We wouldn't have to cover all possible cases for it to be useful.  If
there's some low-hanging fruit here, +1 for getting that.  The cases
which would most often save staff here some time are switching a
varchar to a longer maximum length or switching between a domain which
is varchar to plain varchar (or vice versa).

-Kevin

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Altering a column type - Most efficient way
Next
From: Luke Lonergan
Date:
Subject: Re: 3ware vs Areca