Re: How to do faster DML

On 2024-02-13 01:53:25 +0530, veem v wrote:
> On Mon, 12 Feb 2024 at 03:40, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
>
> The fixed width types are those that the CPU can directly process:
> Integers with 16, 32 and 64 bits, floating point numbers with 32 and 64
> bits. The CPU can read and write them with a single memory access, it
> can do arithmetic with a single instruction, etc.
>
> Number/Numeric are not native types on any CPU. To read them the CPU
> needs several memory accesses (probably one per byte unless you get
> really clever) and then it can't do any calculations with them
> directly, instead it has run a subroutine which does operations on
> little chunks and then puts those chunks together again - basically the
> same as you do when you're doing long addition or multiplication on
> paper. So that's not very efficient.
>
>
> So it looks like the fixed length data type(like integer, float) should be the
> first choice while choosing the data type of the attributes wherever possible,
> as these are native types. (Like choosing "Integer/float" over "Numeric",
> "Char" over "Varchar" etc). 

Please do not conflate "char(n)" with native machine types like int or
float. These are very different things. A char(n) is string of fixed but
arbitrary length. This is not something a CPU can process in a single
instruction. It has to go over it character by character.

There is almost never a reason to use char(n). Just use varchar(n) or in
the case of PostgreSQL just varchar or text.

> However I do see even in Oracle databases, we have Integer type too,

Not really. INTEGER is just an alias for NUMBER(38) in Oracle (see for
example
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlqr/Data-Types.html)
It's not the same as an INTEGER in PostgreSQL.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"