The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX rac_import_idx_parcelno ON "rac_import" ("parcelno");
CREATE INDEX rac_import_idx_akcija ON "rac_import" ("akcija");

The optimized query:

WITH t AS (SELECT
        c.pl_number,
        c.def 
    FROM
        rac_import i 
    JOIN
        (SELECT
            t.pl_number,
            (CASE 
                WHEN t.self_bk_lists_id IS NOT NULL THEN 1 
                WHEN t.ad_hoc_parcels_id IS NOT NULL 
                AND t.service IN ('105',
                '138',
                '107',
                '139') THEN 2 
                ELSE 3 END) AS def 
FROM
dblink('hostaddr=x.x.x.x port=xxx dbname=xxxx user=xxxx password=xxxx'::text,
'
            SELECT tr.pl_number, tr.self_bk_lists_id, tr.ad_hoc_parcels_id, s.service 
            FROM cashmodul.transmission_register tr
            LEFT OUTER JOIN scans.scandata05 s ON tr.pl_number = s.parcelno'::text) t(pl_number character varying (14),
self_bk_lists_id integer,
ad_hoc_parcels_id integer,
service character varying)) AS c 
ON i.parcelno = c.pl_number 
WHERE
i.akcija = 3) SELECT
rac_import.def 
FROM
rac_import 
WHERE
rac_import.parcelno = t.pl_number