From: Tom Lane <tgl@sss.pgh.pa.us>
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Fast insert,
 but slow join and updates for table with 4 billion rows
In-reply-to: <ecea7a5ae84345d2bbe12bb9e3f1edf6@nibio.no>
References: 
 <ff268c342e0646f3a2bffc05a29154aa@nibio.no>,<58dff6f9c2e64a8bb82a990ea1910d32@nibio.no>
 <ecea7a5ae84345d2bbe12bb9e3f1edf6@nibio.no>
Comments: In-reply-to Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
	message dated "Mon, 24 Oct 2016 08:11:48 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <21209.1477313528.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 24 Oct 2016 08:52:08 -0400
Message-ID: <21210.1477313528@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Lars Aksel Opsahl <Lars.Opsahl@nibio.no> writes:
> In this example I have two tables one with 4 billion rows and another wit=
h 50000 rows and then I try to do a standard simple join between this two t=
ables and this takes 397391  ms. with this SQL (the query plan is added is =
further down)

This particular query would work a lot better if you had an index on
nora_bc25_observation (point_uid_ref, epoch), ie both join columns
in one index.  I get the impression that that ought to be the primary
key of the table, which would be an even stronger reason to have a
unique index on it.

			regards, tom lane


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance