Обсуждение: Efficient Way to Merge Two Large Tables

Hi,

I have two tables each with nearly 300M rows. There is a 1:1
relationship between the two tables and they are almost always joined
together in queries. The first table has many columns, the second has
a foreign key to the primary key of the first table and one more
column. It is expected that for every row in table1, there is a
corresponding row in table2. We would like to just add the one column
to the first table and drop the second table to allow us to index this
extra column.

This query would work after adding the column to the first table:
UPDATE table1 SET new_column = table2.new_column FROM table2 WHERE
table1.row_id = table2.row_id;

However, this will take much too long, I have not successfully
completed this on our staging server after running it for 3+ days.

Any extended down time is not really an option. Further, there are
many other tables with foreign keys to table1 so dropping it is fairly
complicated and time consuming as the indexes and foreign keys would
all have to be regenerated.

Does anyone have any other ideas on how this can be done in the most
efficient way possible?

Thanks,
--
Joshua Rubin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Jul 13, 2010, at 1:46 PM, Joshua Rubin wrote:

> Hi,
>
> I have two tables each with nearly 300M rows. There is a 1:1
> relationship between the two tables and they are almost always joined
> together in queries. The first table has many columns, the second has
> a foreign key to the primary key of the first table and one more
> column. It is expected that for every row in table1, there is a
> corresponding row in table2. We would like to just add the one column
> to the first table and drop the second table to allow us to index this
> extra column.

Stupid question before you do this: is there a reason the design was split like this? For instance, if the table with
theid and the single field get updated a lot, while the other table almost never changes, maybe this design isn't so
bad.

HI Ben,

> Stupid question before you do this: is there a reason the design was split like this? For instance, if the table with
theid and the single field get updated a lot, while the other table almost never changes, maybe this design isn't so
bad.

We just wanted to minimize changes to table1, thats why table2 was
added as a separate table and not a new column. Now we need to index
items in table1 with the column in table2 because some queries are
much too slow and would be sped up greatly with this change. In
retrospect, we should have just added a column to table1. Now, we have
all the data, we just need to move it from table2 to table1.

Thanks,
--
Joshua Rubin

Joshua Rubin wrote:

> I have two tables each with nearly 300M rows. There is a 1:1
> relationship between the two tables and they are almost always joined
> together in queries. The first table has many columns, the second has
> a foreign key to the primary key of the first table and one more
> column. It is expected that for every row in table1, there is a
> corresponding row in table2. We would like to just add the one column
> to the first table and drop the second table to allow us to index this
> extra column.
>
> This query would work after adding the column to the first table:
> UPDATE table1 SET new_column = table2.new_column FROM table2 WHERE
> table1.row_id = table2.row_id;
>
> However, this will take much too long, I have not successfully
> completed this on our staging server after running it for 3+ days.

Can you get the query plan (EXPLAIN) of the update query?  My guess is the
join cost scales superlinearly.

You might be able to chop this up into smaller UPDATEs by limiting the
rows to be updated in each round by the primary key.

E.g.:

  UPDATE table1 SET new_column = table2.new_column FROM table2
    WHERE
      table1.row_id = table2.row_id and
      table1.row_id >= 0e6 and table1.row_id < 1e6 and
      table2.row_id >= 0e6 and table2.row_id < 1e6;

for a moving row_id window.

This has helped me in the past with a similar scenario (where both tables
were partitioned by the PK, but it would presumably still work in the
unpartitioned case).

-Julian

    Joshua Rubin wrote:

> I need to figure out why this is slow, and if there is any faster way.

Have you considered INSERTing into a third table that would replace both
source tables when it's over? The target table would initially have no index.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org