From: Craig Ringer <craig@postnewspapers.com.au>
To: Nikolas Everett <nik9000@gmail.com>
Cc: Gurgel, Flavio <flavio@4linux.com.br>
Cc: Matthew Wakeling <matthew@flymine.org>
Cc: Lefteris <lsidir@gmail.com>
Cc: pgsql-performance@postgresql.org, Ivan Voras <ivoras@freebsd.org>
Subject: Re: Air-traffic benchmark
Date: Fri, 08 Jan 2010 11:45:00 +0800
Message-ID: <4B46AA3C.7000107@postnewspapers.com.au> (raw)
In-Reply-To: <d4e11e981001071011t2e919703paedb8d24c800072c@mail.gmail.com>
References: <21301378.46621262884592029.JavaMail.root@mail.4linux.com.br>
<27470544.48331262887021660.JavaMail.root@mail.4linux.com.br>
<d4e11e981001071011t2e919703paedb8d24c800072c@mail.gmail.com>
On 8/01/2010 2:11 AM, Nikolas Everett wrote:
> This table is totally unnormalized. Normalize it and try again. You'll
> probably see a huge speedup. Maybe even 10x. My mantra has always been
> less data stored means less data to scan means faster scans.
Sometimes one intentionally denormalizes storage, though. JOIN costs can
be considerable too, and if most of the time you're interested in all
the data for a record not just a subset of it, storing it denormalized
is often faster and cheaper than JOINing for it or using subqueries to
fetch it.
Normalization or any other splitting of record into multiple separately
stored records also has costs in complexity, management, the need for
additional indexes, storage of foreign key references, all the extra
tuple headers you need to store, etc.
It's still generally the right thing to do, but it should be thought
about, not just tackled blindly. I only tend to view it as a no-brainer
if the alternative is storing numbered fields ("field0", "field1",
"field2", etc) ... and even then there are exceptions. One of my schema
at the moment has address_line_1 through address_line_4 in a `contact'
entity, and there's absolutely *no* way I'm splitting that into a
separate table of address_lines accessed by join and sort! (Arguably I
should be using a single `text' field with embedded newlines instead,
though).
Sometimes it's even better to hold your nose and embed an array in a
record rather than join to an external table. Purism can be taken too far.
Note that Pg's TOAST mechanism plays a part here, too. If you have a big
`text' field, it's probably going to get stored out-of-line (TOASTed)
anyway, and TOAST is going to be cleverer about fetching it than you
will be using a JOIN. So storing it in-line is likely to be the right
way to go. You can even force out-of-line storage if you're worried.
In the case of this benchmark, even if they split much of this data out
into other tables by reference, it's likely to be slower rather than
faster if they still want the data they've split out for most of their
queries.
--
Craig Ringer
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, nik9000@gmail.com, flavio@4linux.com.br, matthew@flymine.org, lsidir@gmail.com, ivoras@freebsd.org
Subject: Re: Air-traffic benchmark
In-Reply-To: <4B46AA3C.7000107@postnewspapers.com.au>
* 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