CODEWITHSUNDEEP
sqlpostgresqljoin
Juergen
Juergen

Reputation: 342

SQL: UPDATE FROM JOIN much slower than SELECT INTO FROM JOIN

I'm currently analyzing the Bitcoin blockchain. I have the following tables in my postgresql database:

To speed-up my analysis queries I copy columns from tables. There are indexes on tx.id, txout.tx_id, and txout.txout_id. To copy the column txout_id from table txout to table tx, I executed the following queries:

First query:

    UPDATE tx
    SET txout_id = txout.txout_id
    FROM tx as t1
    INNER JOIN txout
    ON t1.id = txout.tx_id
Second query:
    SELECT tx.*, txout.txout_id 
    INTO tx_txoutID
    FROM tx
    INNER JOIN txout
    ON tx.id = txout.tx_id

I cancelled the first query after 75 minutes. The second query was finished after 20 minutes. The second query requires that I drop tx, rename tx_txoutID and create indexes for tx afterwards. Now I'm wondering whether there's a query, that's as fast as the second and as comfortable as the first query.

Edit: txout_id is originally not a part of tx, but has been added for the first statement by altering the table.

Upvotes: 4

Views: 1503

Answers (1)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28541

Try this query:

UPDATE tx
SET txout_id = txout.txout_id
FROM txout
WHERE tx.id = txout.tx_id

You do not need to add original table to FROM clause. Unless you intend an additional self join.

Upvotes: 3

Related Questions