From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Stephen Froehlich <s(dot)froehlich(at)cablelabs(dot)com>, "pgsql-novice(at)lists(dot)postgresql(dot)org" <pgsql-novice(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Joining 1-minute data with 5-minute data |
Date: | 2020-01-28 08:43:57 |
Message-ID: | fc0e95a1511675344402c50f9a2eb27a4a04afb5.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Mon, 2020-01-27 at 16:35 +0000, Stephen Froehlich wrote:
> I have a couple of relatively large tables, each with 100-500 million lines (at least in each monthly partition).
>
> One has data every 1 minute, and the other has data every 5 minutes, and I’d like to be able to
> join them (i.e. with each minute in the 5-minute span rounded down to the beginning of that 5-minute interval).
>
> I’m currently running PostgreSQL 11. An upgrade to 12 (for calculated fields) is possible but annoying at the moment.
> (i.e. I’ll do it if its worth it, but I’m otherwise planning on holding off until the Ubuntu 20.40LTS release for that upgrade process.)
>
> What is the most efficient (i.e. performant) way to do that join?
> - Create an index for the 1-min table something like (trunc(time_stamp::int / 300) * 300)::timestamp with time zone
> - Is there a more efficient way to round to 5 minutes?
> - Encode the time stamp for the 5-min table as a tstzrange and create a gist index on that column?
> - Manually add a 5-minute rounded column to the 1-minute table and index that?
> - Something I have missed entirely?
Depending on the number of rows required from each table, an index
may not be useful at all: with a hash join, indexes don't help.
You should make sure that the join condition looks like this:
(expression with columns of the 1-minute table) =
(expression with columns of the 5-minute table)
Otherwise, PostgreSQL can only use a nested loop join, which may
not be the best strategy.
Then experiment with indexes on the expressions in the join condition:
nested loop joins and merge joins can profit from them.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Froehlich | 2020-01-28 17:07:37 | RE: Joining 1-minute data with 5-minute data |
Previous Message | Stephen Froehlich | 2020-01-27 16:35:06 | Joining 1-minute data with 5-minute data |