Query order of magnitude slower with slightly different where clause

SELECT *
FROM claim_totals
WHERE claim_id IN ('324d2af8-46b3-45ad-b56a-0a49d0345653', 'e8a38718-7997-4304-bbfa-138deb84aa82')
(5460 ms)

SELECT claim.claim_id,
    COALESCE(lumpsum.lumpsum_count, 0::bigint)::integer AS lumpsum_count,
    COALESCE(lumpsum.requested_amount, 0::numeric) AS lumpsum_requested_total,
    COALESCE(lumpsum.allowed_amount, 0::numeric) AS lumpsum_allowed_total,
    COALESCE(claim_product.product_count, 0::bigint)::integer + COALESCE(claim_adhoc_product.adhoc_product_count, 0::bigint)::integer AS product_count,
    COALESCE(claim_product.requested_amount, 0::numeric) + COALESCE(claim_adhoc_product.requested_amount, 0::numeric) AS product_requested_amount,
    COALESCE(claim_product.allowed_amount, 0::numeric) + COALESCE(claim_adhoc_product.allowed_amount, 0::numeric) AS product_allowed_amount,
    COALESCE(claim_product.requested_amount, 0::numeric) + COALESCE(claim_adhoc_product.requested_amount, 0::numeric) + COALESCE(lumpsum.requested_amount, 0::numeric) AS requested_total,
    COALESCE(claim_product.allowed_amount, 0::numeric) + COALESCE(claim_adhoc_product.allowed_amount, 0::numeric) + COALESCE(lumpsum.allowed_amount, 0::numeric) AS allowed_total
   FROM claim
     LEFT JOIN ( SELECT claim_lumpsum.claim_id,
            count(claim_lumpsum.claim_lumpsum_id) AS lumpsum_count,
            sum(claim_lumpsum.requested_amount) AS requested_amount,
            sum(claim_lumpsum.allowed_amount) AS allowed_amount
           FROM claim_lumpsum
          GROUP BY claim_lumpsum.claim_id) lumpsum ON lumpsum.claim_id = claim.claim_id
     LEFT JOIN ( SELECT claim_product_1.claim_id,
            count(claim_product_1.claim_product_id) AS product_count,
            sum(claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate) AS requested_amount,
            sum(claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate) AS allowed_amount
           FROM claim_product claim_product_1
          GROUP BY claim_product_1.claim_id) claim_product ON claim_product.claim_id = claim.claim_id
     LEFT JOIN ( SELECT claim_adhoc_product_1.claim_id,
            count(claim_adhoc_product_1.claim_adhoc_product_id) AS adhoc_product_count,
            sum(claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate) AS requested_amount,
            sum(claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate) AS allowed_amount
           FROM claim_adhoc_product claim_adhoc_product_1
          GROUP BY claim_adhoc_product_1.claim_id) claim_adhoc_product ON claim_adhoc_product.claim_id = claim.claim_id;

Nested Loop Left Join  (cost=0.97..149.46 rows=2 width=232) (actual time=0.285..0.289 rows=1 loops=1)
  Output: claim.claim_id, (COALESCE((count(claim_lumpsum.claim_lumpsum_id)), 0::bigint))::integer, COALESCE((sum(claim_lumpsum.requested_amount)), 0::numeric), COALESCE((sum(claim_lumpsum.allowed_amount)), 0::numeric), ((COALESCE((count(claim_product_1.claim_product_id)), 0::bigint))::integer + (COALESCE((count(claim_adhoc_product_1.claim_adhoc_product_id)), 0::bigint))::integer), (COALESCE((sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), 0::numeric)), (COALESCE((sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))), 0::numeric)), ((COALESCE((sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), 0::numeric)) + COALESCE((sum(claim_lumpsum.requested_amount)), 0::numeric)), ((COALESCE((sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))), 0::numeric)) + COALESCE((sum(claim_lumpsum.allowed_amount)), 0::numeric))
  Join Filter: (claim_lumpsum.claim_id = claim.claim_id)
  ->  Nested Loop Left Join  (cost=0.97..135.31 rows=1 width=160) (actual time=0.260..0.264 rows=1 loops=1)
        Output: claim.claim_id, (count(claim_product_1.claim_product_id)), (sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), (sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))), (count(claim_adhoc_product_1.claim_adhoc_product_id)), (sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), (sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate)))
        Join Filter: (claim_adhoc_product_1.claim_id = claim.claim_id)
        ->  Nested Loop Left Join  (cost=0.97..122.14 rows=1 width=88) (actual time=0.254..0.256 rows=1 loops=1)
              Output: claim.claim_id, (count(claim_product_1.claim_product_id)), (sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), (sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate)))
              Join Filter: (claim_product_1.claim_id = claim.claim_id)
              ->  Index Only Scan using claim_pkey on client_pinnacle.claim  (cost=0.42..1.54 rows=1 width=16) (actual time=0.078..0.079 rows=1 loops=1)
                    Output: claim.claim_id
                    Index Cond: (claim.claim_id = 'e8a38718-7997-4304-bbfa-138deb84aa82'::uuid)
                    Heap Fetches: 0
              ->  GroupAggregate  (cost=0.55..120.58 rows=1 width=54) (actual time=0.163..0.163 rows=1 loops=1)
                    Output: claim_product_1.claim_id, count(claim_product_1.claim_product_id), sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate)), sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))
                    Group Key: claim_product_1.claim_id
                    ->  Index Scan using claim_product_claim_id_product_id_distributor_company_id_lo_key on client_pinnacle.claim_product claim_product_1  (cost=0.55..118.99 rows=105 width=54) (actual time=0.071..0.091 rows=12 loops=1)
                          Output: claim_product_1.claim_id, claim_product_1.claim_product_id, claim_product_1.rebate_requested_quantity, claim_product_1.rebate_requested_rate, claim_product_1.rebate_allowed_quantity, claim_product_1.rebate_allowed_rate
                          Index Cond: (claim_product_1.claim_id = 'e8a38718-7997-4304-bbfa-138deb84aa82'::uuid)
        ->  GroupAggregate  (cost=0.00..13.15 rows=1 width=160) (actual time=0.001..0.001 rows=0 loops=1)
              Output: claim_adhoc_product_1.claim_id, count(claim_adhoc_product_1.claim_adhoc_product_id), sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate)), sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))
              Group Key: claim_adhoc_product_1.claim_id
              ->  Seq Scan on client_pinnacle.claim_adhoc_product claim_adhoc_product_1  (cost=0.00..13.12 rows=1 width=160) (actual time=0.001..0.001 rows=0 loops=1)
                    Output: claim_adhoc_product_1.claim_adhoc_product_id, claim_adhoc_product_1.claim_id, claim_adhoc_product_1.product_name, claim_adhoc_product_1.product_number, claim_adhoc_product_1.uom_type_description, claim_adhoc_product_1.rebate_requested_quantity, claim_adhoc_product_1.rebate_requested_rate, claim_adhoc_product_1.rebate_allowed_quantity, claim_adhoc_product_1.rebate_allowed_rate, claim_adhoc_product_1.claimant_contract_name, claim_adhoc_product_1.resolve_date
                    Filter: (claim_adhoc_product_1.claim_id = 'e8a38718-7997-4304-bbfa-138deb84aa82'::uuid)
  ->  GroupAggregate  (cost=0.00..14.05 rows=2 width=96) (actual time=0.001..0.001 rows=0 loops=1)
        Output: claim_lumpsum.claim_id, count(claim_lumpsum.claim_lumpsum_id), sum(claim_lumpsum.requested_amount), sum(claim_lumpsum.allowed_amount)
        Group Key: claim_lumpsum.claim_id
        ->  Seq Scan on client_pinnacle.claim_lumpsum  (cost=0.00..14.00 rows=2 width=96) (actual time=0.000..0.000 rows=0 loops=1)
              Output: claim_lumpsum.claim_lumpsum_id, claim_lumpsum.claim_id, claim_lumpsum.lumpsum_id, claim_lumpsum.requested_amount, claim_lumpsum.allowed_amount, claim_lumpsum.event_date_range, claim_lumpsum.contract_lumpsum_id, claim_lumpsum.claimant_contract_name, claim_lumpsum.hint_contract_lumpsum_description
              Filter: (claim_lumpsum.claim_id = 'e8a38718-7997-4304-bbfa-138deb84aa82'::uuid)
Planning time: 6.336 ms
Execution time: 0.753 ms

Hash Right Join  (cost=81278.79..81674.85 rows=2 width=232) (actual time=5195.972..5458.916 rows=2 loops=1)
  Output: claim.claim_id, (COALESCE((count(claim_lumpsum.claim_lumpsum_id)), 0::bigint))::integer, COALESCE((sum(claim_lumpsum.requested_amount)), 0::numeric), COALESCE((sum(claim_lumpsum.allowed_amount)), 0::numeric), ((COALESCE((count(claim_product_1.claim_product_id)), 0::bigint))::integer + (COALESCE((count(claim_adhoc_product_1.claim_adhoc_product_id)), 0::bigint))::integer), (COALESCE((sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), 0::numeric)), (COALESCE((sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))), 0::numeric)), ((COALESCE((sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), 0::numeric)) + COALESCE((sum(claim_lumpsum.requested_amount)), 0::numeric)), ((COALESCE((sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))), 0::numeric)) + COALESCE((sum(claim_lumpsum.allowed_amount)), 0::numeric))
  Hash Cond: (claim_product_1.claim_id = claim.claim_id)
  ->  HashAggregate  (cost=81231.48..81438.09 rows=13774 width=54) (actual time=5182.546..5405.990 rows=95763 loops=1)
        Output: claim_product_1.claim_id, count(claim_product_1.claim_product_id), sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate)), sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))
        Group Key: claim_product_1.claim_id
        ->  Seq Scan on client_pinnacle.claim_product claim_product_1  (cost=0.00..55253.59 rows=1731859 width=54) (actual time=0.020..1684.826 rows=1731733 loops=1)
              Output: claim_product_1.claim_id, claim_product_1.claim_product_id, claim_product_1.rebate_requested_quantity, claim_product_1.rebate_requested_rate, claim_product_1.rebate_allowed_quantity, claim_product_1.rebate_allowed_rate
  ->  Hash  (cost=47.29..47.29 rows=2 width=160) (actual time=0.110..0.110 rows=2 loops=1)
        Output: claim.claim_id, (count(claim_lumpsum.claim_lumpsum_id)), (sum(claim_lumpsum.requested_amount)), (sum(claim_lumpsum.allowed_amount)), (count(claim_adhoc_product_1.claim_adhoc_product_id)), (sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), (sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate)))
        Buckets: 1024  Batches: 1  Memory Usage: 1kB
        ->  Hash Right Join  (cost=41.53..47.29 rows=2 width=160) (actual time=0.105..0.108 rows=2 loops=1)
              Output: claim.claim_id, (count(claim_lumpsum.claim_lumpsum_id)), (sum(claim_lumpsum.requested_amount)), (sum(claim_lumpsum.allowed_amount)), (count(claim_adhoc_product_1.claim_adhoc_product_id)), (sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), (sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate)))
              Hash Cond: (claim_adhoc_product_1.claim_id = claim.claim_id)
              ->  HashAggregate  (cost=16.25..19.25 rows=200 width=160) (actual time=0.001..0.001 rows=0 loops=1)
                    Output: claim_adhoc_product_1.claim_id, count(claim_adhoc_product_1.claim_adhoc_product_id), sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate)), sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))
                    Group Key: claim_adhoc_product_1.claim_id
                    ->  Seq Scan on client_pinnacle.claim_adhoc_product claim_adhoc_product_1  (cost=0.00..12.50 rows=250 width=160) (actual time=0.001..0.001 rows=0 loops=1)
                          Output: claim_adhoc_product_1.claim_adhoc_product_id, claim_adhoc_product_1.claim_id, claim_adhoc_product_1.product_name, claim_adhoc_product_1.product_number, claim_adhoc_product_1.uom_type_description, claim_adhoc_product_1.rebate_requested_quantity, claim_adhoc_product_1.rebate_requested_rate, claim_adhoc_product_1.rebate_allowed_quantity, claim_adhoc_product_1.rebate_allowed_rate, claim_adhoc_product_1.claimant_contract_name, claim_adhoc_product_1.resolve_date
              ->  Hash  (cost=25.25..25.25 rows=2 width=88) (actual time=0.093..0.093 rows=2 loops=1)
                    Output: claim.claim_id, (count(claim_lumpsum.claim_lumpsum_id)), (sum(claim_lumpsum.requested_amount)), (sum(claim_lumpsum.allowed_amount))
                    Buckets: 1024  Batches: 1  Memory Usage: 1kB
                    ->  Hash Right Join  (cost=19.49..25.25 rows=2 width=88) (actual time=0.088..0.092 rows=2 loops=1)
                          Output: claim.claim_id, (count(claim_lumpsum.claim_lumpsum_id)), (sum(claim_lumpsum.requested_amount)), (sum(claim_lumpsum.allowed_amount))
                          Hash Cond: (claim_lumpsum.claim_id = claim.claim_id)
                          ->  HashAggregate  (cost=16.40..19.40 rows=200 width=96) (actual time=0.003..0.003 rows=0 loops=1)
                                Output: claim_lumpsum.claim_id, count(claim_lumpsum.claim_lumpsum_id), sum(claim_lumpsum.requested_amount), sum(claim_lumpsum.allowed_amount)
                                Group Key: claim_lumpsum.claim_id
                                ->  Seq Scan on client_pinnacle.claim_lumpsum  (cost=0.00..13.20 rows=320 width=96) (actual time=0.001..0.001 rows=0 loops=1)
                                      Output: claim_lumpsum.claim_lumpsum_id, claim_lumpsum.claim_id, claim_lumpsum.lumpsum_id, claim_lumpsum.requested_amount, claim_lumpsum.allowed_amount, claim_lumpsum.event_date_range, claim_lumpsum.contract_lumpsum_id, claim_lumpsum.claimant_contract_name, claim_lumpsum.hint_contract_lumpsum_description
                          ->  Hash  (cost=3.07..3.07 rows=2 width=16) (actual time=0.073..0.073 rows=2 loops=1)
                                Output: claim.claim_id
                                Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                ->  Index Only Scan using claim_pkey on client_pinnacle.claim  (cost=0.42..3.07 rows=2 width=16) (actual time=0.048..0.070 rows=2 loops=1)
                                      Output: claim.claim_id
                                      Index Cond: (claim.claim_id = ANY ('{324d2af8-46b3-45ad-b56a-0a49d0345653,e8a38718-7997-4304-bbfa-138deb84aa82}'::uuid[]))
                                      Heap Fetches: 0
Planning time: 1.020 ms
Execution time: 5459.461 ms