pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter J. Holzer <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: How to do faster DML
Date: Fri, 16 Feb 2024 00:31:30 +0100
Message-ID: <20240215233130.mutkz2rcjehhtixg@hjp.at> (raw)
In-Reply-To: <CAKFQuwZ4qqf-B9b8uQ5Xrxge9Rn0eeAdDPsn-WCwRBGBqNp01A@mail.gmail.com>
References: <20240211133203.zw6ezhdzefyxmkd7@hjp.at>
	<CAB+=1TVP9xFM16J_ipQaL05UYQPAYyP51xzOSnvcmppBLKvYpA@mail.gmail.com>
	<CANzqJaDOjP=DY8ArVae0py_iSiS+JneQatB7seRhU29y7DqbXw@mail.gmail.com>
	<20240211222230.tt3mpjdvgrimeodu@hjp.at>
	<CAB+=1TVjov+y-8=d3ey-B7-Wj-23KPAKQQAejkjXcS50_KZ=pg@mail.gmail.com>
	<CAKAnmmLcTS7zpeZ3m7mkJxGsu+f+Tv9bp5KV3T52Q+c8DMTm+A@mail.gmail.com>
	<CAB+=1TXv=RNOttpmzJOwbczSsaS0VnigFMLkPVY051fiVAxPdg@mail.gmail.com>
	<20240213150234.ydiitfoowojhj2n3@hjp.at>
	<CAB+=1TVCHMPjOS-uoFj36zBFPTzybY+VhrRC8WTa0pgg-0_xhA@mail.gmail.com>
	<CAKFQuwZ4qqf-B9b8uQ5Xrxge9Rn0eeAdDPsn-WCwRBGBqNp01A@mail.gmail.com>

On 2024-02-14 22:55:01 -0700, David G. Johnston wrote:
> On Tuesday, February 13, 2024, veem v <veema0000@gmail.com> wrote:
> 
>     float data types rather than numeric. This will give better performance.
> 
> 
>  Only use an inexact floating-point data type if you truly understand what you
> are getting yourself into.  Quickly getting the wrong answer isn’t tolerable
> solution.

Do NOT assume that a decimal type (even if it can grow to ridiculuous
lengths like PostgreSQL's numeric) is exact in the mathematical sense.
It isn't. It cannot represent almost all real numbers. No pi or e of
course, but also no seemingly simple fractions like 1/3 or 1/7.

Unless you never divide anything, you will need to be aware of the
rounding behaviour, just as you have to with binary floating point
types. And if you use a finite precision you will also have to deal with
rounding on multiplication (and possibly even addition and subtraction,
if you use different precisions).

Almost 40 years ago, our numerical methods professor started his first
lecture with the sentence "You can use a computer for anything - except
computing". He spent the rest of the semester proving himself wrong,
of course, but computing correctly is hard - and choosing a data type
which more closely mimics the way we learn to compute in primary school
doesn't necessarily make it easier. Mostly it just makes it harder to
spot the errors ;-).

        hp

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

Attachments:

  signature.asc (application/pgp-signature, 832B) download

view thread (67+ messages)  latest in thread

Message-ID: <20240215233130.mutkz2rcjehhtixg@hjp.at>
Permalink:  ../20240215233130.mutkz2rcjehhtixg@hjp.at/
Also on:    postgresql.org/message-id/20240215233130.mutkz2rcjehhtixg@hjp.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: hjp-pgsql@hjp.at
  Subject: Re: How to do faster DML
  In-Reply-To: <20240215233130.mutkz2rcjehhtixg@hjp.at>
  References: <20240211133203.zw6ezhdzefyxmkd7@hjp.at> <CAB+=1TVP9xFM16J_ipQaL05UYQPAYyP51xzOSnvcmppBLKvYpA@mail.gmail.com> <CANzqJaDOjP=DY8ArVae0py_iSiS+JneQatB7seRhU29y7DqbXw@mail.gmail.com> <20240211222230.tt3mpjdvgrimeodu@hjp.at> <CAB+=1TVjov+y-8=d3ey-B7-Wj-23KPAKQQAejkjXcS50_KZ=pg@mail.gmail.com> <CAKAnmmLcTS7zpeZ3m7mkJxGsu+f+Tv9bp5KV3T52Q+c8DMTm+A@mail.gmail.com> <CAB+=1TXv=RNOttpmzJOwbczSsaS0VnigFMLkPVY051fiVAxPdg@mail.gmail.com> <20240213150234.ydiitfoowojhj2n3@hjp.at> <CAB+=1TVCHMPjOS-uoFj36zBFPTzybY+VhrRC8WTa0pgg-0_xhA@mail.gmail.com> <CAKFQuwZ4qqf-B9b8uQ5Xrxge9Rn0eeAdDPsn-WCwRBGBqNp01A@mail.gmail.com> <20240215233130.mutkz2rcjehhtixg@hjp.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox