Advice on tuning a join/lookup against a very large PostgreSQL table?

EinarRoed — Tue, 26 Oct 2021 06:55:11 GMT

I want to identify all rows in the table SOURCE which don't exist in the table TARGET. This is easily done via this simple code:

proc sql;
	create table NEW as
	select src.primkey from SOURCE src
	left join TARGET tgt on (src.primkey = tgt.primkey)
	where tgt.primkey is missing;
quit;

SOURCE is a SAS-table that contains around 3 million rows.

TARGET is a PostgreSQL-table that contains around 750 million rows. It has an index on primkey.

Do you know if there's a more efficient way to perform this operation? What's the most optimized way to check if a source row exists in a very large target table?

Re: Advice on tuning a join/lookup against a very large PostgreSQL table?

Kurt_Bremser — Tue, 26 Oct 2021 07:33:02 GMT

Usually the best way is to upload the SAS table to a temporary table in the database and do the join there with explicit pass through. This minimizes network traffic.

topic Re: Advice on tuning a join/lookup against a very large PostgreSQL table? in SAS Programming

Advice on tuning a join/lookup against a very large PostgreSQL table?

Re: Advice on tuning a join/lookup against a very large PostgreSQL table?