Clever Geek Handbook

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

I am currently analyzing bitcoin blockchain. I have the following tables in my postgresql database:

  • tx: 11GB
  • txin: 40GB
  • txout: 37GB

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

First request: 

     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 canceled the first request after 75 minutes. The second request was completed in 20 minutes. The second query requires me to delete the tx, rename the tx_txoutID, and create indexes for the tx afterwards. Now I am wondering if there is a query that is as fast as the second one and as convenient as the first query.

Edit: txout_id is not originally part of tx, but was added for the first statement, changing the table.

+3


source to share


1 answer


Try this query:

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


You don't need to add the original table to FROM

. Unless you intend to join yourself.

+1


source






More articles:


All Articles