Speed up a join with 2 keys

Question

Can I speed this somehow up?

CREATE TABLE TEST AS 
SELECT t1.Tech, t2.Coloar,t2.Car from Table1 t1 
INNER JOIN Table2 t2 on (t1.ID = t2.ID AND t1.IT = t2.IT AND t1.LIFI = t2.LIFI) OR (t1.RA = t2.RA) 
where...

If I create the table just with

(t1.ID = t2.ID AND t1.IT = t2.IT AND t1.LIFI = t2.LIFI)

or with this key

(t1.RA = t2.RA)

it takes seconds, but both together a couple of minutes and I have more and bigger tables to create and sometimes I need to LEFT JOIN this key pair, like

CREATE TABLE...
INNER JOIN...
LEFT JOIN on (t1.ID = t2.ID AND t1.IT = t2.IT AND t1.LIFI = t2.LIFI) OR (t1.RA = t2.RA)

Do you have any indexes on the `ID, IT`, `LIFI` and `RA` columns? — Rigerta, Jun 28 '18 at 08:38
I dont know...how to find that out? Just got the tables to work with. — NoOne, Jun 28 '18 at 08:39
On [this answer](https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql) you can see how you can check existing indexes. Try it to see if you have indexes on the tables included in the join. — Rigerta, Jun 28 '18 at 08:40

score 1 · Accepted Answer · answered Jun 28 '18 at 11:15

Remove the OR from the ON clause:

CREATE TABLE TEST AS 
    SELECT t1.Tech,
           COALESCE(t2.Color, tt2.Color),
           COLAESCE(t2.Car, tt2.Car)
    FROM Table1 t1 LEFT JOIN
         Table2 t2 
         ON t1.ID = t2.ID AND t1.IT = t2.IT AND t1.LIFI = t2.LIFI LEFT JOIN
         Table2 tt2
         ON t1.RA = tt2.RA AND t2.ID IS NULL
     WHERE (t2.ID IS NOT NULL OR tt2.ID IS NOT NULL) AND
           ...

This should be able to take advantage of appropriate indexes for both the joins.

score 0 · Answer 2 · answered Jun 28 '18 at 09:01

0

Try this

CREATE TABLE TEST AS 
select t1.Tech, t2.Coloar,t2.Car
From Table1  t1
left join Table2 t2 on (case when t1.RA = t2.RA then 1 when t1.ID = t2.ID AND t1.IT = t2.IT AND t1.LIFI = t2.LIFI then 1 else 0 end = 1)

answered Jun 28 '18 at 09:01

Gaj

888
5
5

Takes 40 seconds longer – NoOne Jun 28 '18 at 09:26
generally left join will be costly. what is your benchmark – Gaj Jun 28 '18 at 09:37
~5:00 Minutes vs ~5:40...on a small table - with one key pair the table is done in seconds. The bigger table need over an hour. With just one key pair the bigger table is done in under one minute. – NoOne Jun 28 '18 at 09:45

Speed up a join with 2 keys

2 Answers2