Copying Rows of Table0 to Table2 Where Same Rows Do Not Exist in Table1 (PostgreSQL)

Question

Could anyone please tell me which one of the following is more efficient? I have tens of millions of rows to process, and performance is critical.

In the second example, table0 is a temporary table, which seems to be much faster to create than table0 in the first example. (Why?) I couldn't use a temporary table in the first example because the variable row could not be declared before the table is created. (table0 holds all distinct rows of the original table, which is not shown in the code below.)

I guess it'd be a good idea to create hash indices for blah2, blah3, blah4 and blah5 of table1 in the second example, though it would then take longer to write onto the table.

---

FOR row IN SELECT * FROM table0
LOOP
  IF NOT EXISTS (SELECT 1 FROM table1
                   WHERE blah2 = row.blah2 AND blah3 = row.blah3
                     AND blah4 = row.blah4 AND blah5 = row.blah5) THEN
    INSERT INTO table2
      (blah0, blah1, blah2, blah3, blah4, blah5)
      VALUES (row.blah0, row.blah1, row.blah2, row.blah3, row.blah4, row.blah5);
  END IF;
END LOOP;

---

INSERT INTO table2
  (blah0, blah1, blah2, blah3, blah4, blah5)
  SELECT blah0, blah1, blah2, blah3, blah4, blah5 FROM table0
    WHERE NOT EXISTS
      (SELECT 1 FROM table1
         WHERE table1.blah2 = table0.blah2
           AND table1.blah3 = table0.blah3
           AND table1.blah4 = table0.blah4
           AND talbe1.blah5 = table0.blah5);

Answer 1

Questions

In the second example, table0 is a temporary table, which seems to be much faster to create than table0 in the first example. (Why?)

Temporary tables are regularly much faster than regular ones as their content is not persisted to disk. You still have minor disk activity when you create one, as system catalogs receive entries.

Performance with temp tables takes a dive as soon as you run out of temporary buffers because the system starts swapping out pages to disk. You can set temp_buffers at the start of your session to provide more RAM for temp tables. More details in this related answer.

If in the end you want to persist the result somewhere, you might as well use a regular table right away in the second example.

The first example is also much slower, because looping though individual rows is regularly much more expensive than set-based operations with SQL-commands.

I couldn't use a temporary table in the first example because the variable row could not be declared before the table is created. (table0 holds all distinct rows of the original table, which is not shown in the code below.)

Well, you can't use the row type in a function before the table is created. But you can just use an anonymous record instead:

DECLARE
   rec record;
BEGIN
   FOR rec IN SELECT * FROM table0 ...

Alternative solution

But your first approach is no good anyway. Your second approach looks just fine. Should be the fastest way. An alternative would be:

INSERT INTO table2 (blah0, blah1, blah2, blah3, blah4, blah5)
SELECT t0.blah0, t0.blah1, t0.blah2, t0.blah3, t0.blah4, t0.blah5
FROM   table0 t0
LEFT   JOIN table1 t1 USING (blah2, blah3, blah4, blah5)
WHERE  t1.t1_id IS NULL -- or any other column defined NOT NULL

If you have duplicate entries for (blah2, blah3, blah4, blah5) in table1 then your query with EXISTS is probably faster. Else this one with LEFT JOIN / IS NULL might come out on top.

Index and test

Of course an index on (blah2, blah3, blah4, blah5) in table1 would help a lot - adding some cost to write operations in table1. Make that a plain b-tree index (like @Craig already advises in the comment), but I would go for a multi-column index. Cheaper and faster for this case!

Use EXPLAIN ANALYZE to test performance as @Craig advised in the comment - or just EXPLAIN first (without ANALYZE), since your tables seem to be huge and EXPLAIN ANALYZE fake-executes it all to get real times.