Re: Fast insert, but slow join and updates for table with 4 billion rows

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Fast insert, but slow join and updates for table with 4 billion rows
Date: 2016-10-24 12:52:08
Message-ID: 21210.1477313528@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no> writes:
> In this example I have two tables one with 4 billion rows and another with 50000 rows and then I try to do a standard simple join between this two tables 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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Lars Aksel Opsahl 2016-10-24 20:07:35 Re: Fast insert, but slow join and updates for table with 4 billion rows
Previous Message Lars Aksel Opsahl 2016-10-24 08:11:48 Fast insert, but slow join and updates for table with 4 billion rows