Re: MERGE: performance advices

Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> writes:

> I need to merge 2 tables:
>
> update d set c1=s.c1, c2=s.c2... from s where d.pk=s.pk;
> insert into d (pk, c1, c2, ...) select pk, c1, c2, c3 from s
> where s.pk not in (select pk from d);

you could try making the not in an exists. In released versions of Postgres
sometimes one is better than the other. Raising work_mem might matter if it
lets you do a hash join for either the IN/EXISTS or the join.

There is another approach though whether it's faster depends on how many
indexes you have and other factors:

CREATE TABLE new_d AS
SELECT DISTINCT ON (pk) pk,c1,c
FROM (select 1 as t, * from s
union all
select 2 as t, * from d
)
ORDER BY pk, t

This will pull in all the rows from both tables and sort them by pk with
records from s appearing before matching records from t and then keep only the
first value for each pk.

Then you'll have to build indexes, swap the tables, and fix any views or rules
which refer to the old table (they'll still refer to the old table, not the
new table even after renaming it to the old name).

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!