Subquery Applying Incorrect Predicate?
The big difference in the serial plan that I can see is this expensive TOP operator that has been introduced by the optimizer:



The TOP is probably there due to row goals, there is a 4-part series on this at SQLPerformance.com:


Setting and Identifying Row Goals in Execution Plans
Row Goals, Part 2: Semi Joins
Row Goals, Part 3: Anti Joins
Row Goals, Part 4: The Anti Join Anti Pattern


(Part 1 and maybe part 2 are relevant for this query.)

I can't confirm for sure in your case because you're using 2016 and the showplan properties that expose row goal information weren't added until 2017 CU3 (and were added in 2016 SP2, just released this week). You can see them if you show the output from running the original, serial query on the server adding the following (taken from part 1 of the series above):

OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607, QUERYTRACEON 8612);


Estimates are different all over the place, so that tells me that data and/or stats are different, but probably mostly stats since local and server both give the same actual rows.

I suggest a rewrite, changing IN to EXISTS, simplifying the subquery (a lot of redundant work there), and removing the GROUP BY (which is unnecessary busy work, and may be the culprit here):

SELECT p.id_prov,
       p.name,
       ps.val
FROM dbo.provider_payment pp
INNER JOIN dbo.provider p 
  ON p.id_prov = pp.id_prov
INNER JOIN dbo.payment_settings ps 
  ON ps.id_prov_payment = pp.id_prov_payment
WHERE pp.payment_method = 'PROTX'
  AND ps.setting = 'VendorName'
  AND p.is_deleted = 0
  AND p.status = 'Y'
  AND EXISTS
  (
    SELECT 1
      FROM dbo.deposit_payment dp
      WHERE dp.id_prov = p.id_prov
        AND dp.payment_method = pp.payment_method
        AND dp.create_date &gt; '2018-01-01'
  );


Updating stats can't hurt, but let us know if the rewrite helps. You could also try hinting the newer cardinality estimator using OPTION (QUERYTRACEON 2312); and in case you are hit by anything that has since been fixed in the optimizer you can also try 4199.

Leaving the local variance out, the two plans on the server have the same predicate on the scan of deposit_payment, except that the parallel plan uses a probe for id_prov. So I really don't think the predicate has anything to do with any performance differences:



The only difference between server and local is that the local serial plan handles the id_prov filter later, in a hash match. Exercise to the reader to determine if that's better or worse. :-)

I would focus on writing simpler queries without unnecessary and/or redundant logic (and perhaps introducing a supporting index on deposit_payment to help this type of query without a scan/predicate) rather than trying to discover bugs. :-)