0

I have this query

INSERT INTO interactions(
    user_id, external_object_id, origin, interaction_type, 
    interaction_sub_type, published_at, origin_id, created_at, updated_at)

SELECT find_user(id), find_external_object(external_object_id), 'facebook' as origin,
    interaction_type, interaction_sub_type, published_at, origin_id, 
    current_date, current_date
from legacy_table

The legacy_table have around 9 million records, and it is running for around 20 hours now and find_user and find_external_object are functions that look for the return the id of an user and an external object (of course) based on id from legacy table, like this

SELECT legacy_user.uid into uid 
    from legacy_user 
    where legacy_user.id = $1;

SELECT users.id into user_id 
    from users 
    where users.uid = uid;

RETURN user_id;

What can I do to improve the query?

4
  • 1
    Why don't you just put the joins into the queries instead of embedding them in functions? – Colin 't Hart May 29 '15 at 21:50
  • 2
    Can you post the function definitions? And when you post a question about performance of a query, always include the execution plan! And please add table definitions including indexes too. – Colin 't Hart May 29 '15 at 21:50
  • @Colin'tHart sure, I can do that tomorrow – Luiz E. May 29 '15 at 22:31
  • 2
    And, of course, the obvious: your version of Postgres. Please read and follow the tag info for [postgresql-performance]. – Erwin Brandstetter May 30 '15 at 0:44
3

Try creating a couple of temporary tables, indexing them, analyzing them, and then join to them..

CREATE TEMPORARY TABLE luid_lookup AS
  SELECT id, find_user(id) AS fuid FROM legacy_table;

CREATE TEMPORARY TABLE eoid_lookup AS
  SELECT external_object_id, find_external_object(external_object_id) AS feid FROM legacy_table;

CREATE INDEX luid_lookup_tmp_ix1 ON luid_lookup (id, fuid);
CREATE INDEX eoid_lookup_tmp_ix1 ON eoid_lookup (external_object_id, feid);

ANALYZE luid_lookup;
ANALYZE eoid_lookup;

And then

INSERT INTO interactions(
    user_id, external_object_id, origin, interaction_type, 
    interaction_sub_type, published_at, origin_id, created_at, updated_at)

SELECT b.fuid, c.feid, 'facebook' as origin,
    a.interaction_type, a.interaction_sub_type, a.published_at, a.origin_id, 
    a.current_date, a.current_date
from legacy_table a
join luid_lookup b on a.id = b.id
join eoid_lookup c on a.external_object_id = c.external_object_id

If the creation of the temporary tables takes a long time, then your find_user() and your find_external_object() methods need to be tuned (as opposed to the actual insert query).

One additional note .. if the id or external_object_id are NOT pkeys/unique of their tables, you will want to make sure that you're only getting unique values.. (If you don't, then you'll get duplicate rows when you join) In that case, make sure you do a subselect and get the distinct result (example below).

CREATE TEMPORARY TABLE luid_lookup AS
  SELECT id, find_user(id) AS fuid FROM (SELECT DISTINCT id FROM legacy_table) l_tab;
3
  • I forgot to mention: those legacy tables are actually connected to my database via postgres_fdw. should I still use this temporary tables? – Luiz E. May 29 '15 at 21:17
  • 1
    I'm not sure if that matters, other than potentially impacting your ability to tune the two methods in question. I could be wrong there, though - I don't have much experience with foreign data wrappers. – Joishi Bodio May 29 '15 at 21:24
  • 1
    I will say this - when I have had long running upsert operations in the past (that were looking in a lot of different places for the data it needed), creating the temporary tables, indexing, and joining made the query run VASTLY faster. (from 30 minutes to under 2 minutes) .. so I think it's worth a try regardless. – Joishi Bodio May 29 '15 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.