I am using PostgreSQL via pgadmin3 on Ubuntu 16.04 with 7.7GiB Memory and 4x.3GHz processor. My only aim with this database is to merge multiple datasets (tables) and extract the final table. I am wondering what slows my join operation down the most: Having many rows, merging on multiple columns, or mergng multiple tables.

I have the following tables:

combinations (121M rows)
source | target | year
bigint | bigint | smallint

source | target | year
bigint | bigint | smallint

source | target | year     | auth_first_order | auth_second_order | com_first_order | com_second_order
bigint | bigint | smallint | double           | double            | double          | double

Eventually there will be more tables. Each row in each table is uniquely identified by (source,target,year). All tables have non-clustered indices on (source, target, year)

My initial idea was to construct a view and export the view to disk. This is the definition of view master:

SELECT comb.source,
FROM combinations comb
 LEFT JOIN coauthorships coauth ON comb.source = coauth.source AND comb.target = coauth.target AND comb.year = coauth.year
 LEFT JOIN adjacencies adj ON comb.source = adj.source AND comb.target = adj.target AND comb.year = adj.year;

Then I proceeded to export the view like so:

psql -U postgres -d department-networks -c "COPY master TO stdout DELIMITER ',' CSV HEADER;" > ~/master.csv

How can I improve the merge design? I have some freedom over the table look like. Given that the tables are not indexed, which method should I use? From pgadmin's help page it looks like both hash and GiST seem appropriate. Should I merge the keys so that I don't merge on multiple columns but just one? Should I join some tables separately so that in the end I only merge two tables? Should I not use postgres at all? I'm grateful for any help in this regard.


Here is the query plan:

"Merge Left Join  (cost=2615359.20..10115676.76 rows=146636992 width=52)"
"  Merge Cond: ((comb.source = adj.source) AND (comb.target = adj.target) AND (comb.year = adj.year))"
"  ->  Merge Left Join  (cost=2048532.06..8406982.78 rows=146636992 width=20)"
"        Merge Cond: ((comb.source = coauth.source) AND (comb.target = coauth.target) AND (comb.year = coauth.year))"
"        ->  Index Only Scan using combinations_source_target_year_idx on combinations comb  (cost=0.57..5107371.45 rows=146636992 width=18)"
"        ->  Materialize  (cost=2047466.59..2106034.64 rows=11713611 width=20)"
"              ->  Sort  (cost=2047466.59..2076750.61 rows=11713611 width=20)"
"                    Sort Key: coauth.source, coauth.target, coauth.year"
"                    ->  Seq Scan on coauthorships coauth  (cost=0.00..191745.11 rows=11713611 width=20)"
"  ->  Materialize  (cost=566827.14..581496.20 rows=2933811 width=50)"
"        ->  Sort  (cost=566827.14..574161.67 rows=2933811 width=50)"
"              Sort Key: adj.source, adj.target, adj.year"
"              ->  Seq Scan on adjacencies adj  (cost=0.00..51115.11 rows=2933811 width=50)"
  • Please consider reading the postgres-performance tag wiki
    – mustaccio
    Dec 6, 2017 at 17:49
  • Regarding your first question, can you please be more speific? What do you mean by queries? Except the the view definition I don't have any other query.
    – MERose
    Dec 6, 2017 at 18:06
  • @RDFozz: It will be repeated but in total not more than say 10 times. combinations contains just those three columns and I want non-matching rows to be emtpy. Hence the left join.
    – MERose
    Dec 6, 2017 at 18:24
  • @MERose - And, are you absolutely certain that source, target and year are unique? You indicated in comments to Evan Carroll's answer that coauthorships and adjacencies do have indexes on those three columns, but not combinations; are these unique indexes? If you're relying on them to be, I'd enforce the assumption. For combinations, the index should be the clustered primary key - if not appropriate as a primary key for the others, then you should at least have the three columns as a unique constraint/index.
    – RDFozz
    Dec 6, 2017 at 19:33
  • Also - is coauth a table in its own right? If not, then you've got it listed twice in your table description in the question.
    – RDFozz
    Dec 6, 2017 at 19:35

1 Answer 1


@EvanCarroll: Good point, I wanted to add this as question. The tables are not indexed as I don't know which method to use. Question edited. – MERose 16 secs ago

Red flag. Stop your query. Add your indexes. Rerun it. Assuming you have no indexes, add these and then paste the result of \d table for coauthorships and adjacencies`.

CREATE INDEX ON combinations ( source, target, year );
CREATE INDEX ON adjacencies ( source, target, year );
CREATE INDEX ON coauthorships ( source, target, year );
VACUUM ANALYZE combinations;
VACUUM ANALYZE adjacencies;
VACUUM ANALYZE coauthorships;

Also why are you using bigint if you only have millions of rows?

  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Dec 7, 2017 at 5:37

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.