Postgres10 picks very slow query plan, when faster plans seem available
Which seems to suggest that its best bet is to load 1.4 mil rows, and take 771 seconds? Am I reading this wrong?


You are reading it wrong in two ways.  It doesn't think it will read 1.4 mil rows.  It thinks it will start a index scan which if completed will read 1.4 mil rows, but that due to the LIMIT 1 it will get to stop very early on. Apparently it is wrong about how early it can stop, but that is what it thinks.

Also, cost estimates are in arbitrary units, not in milliseconds, so it doesn't think it will take 771 seconds.  And the cost at that level is again assuming the index scan runs to completion.  The cost of the top LIMIT node, 5784.38 arbitrary units, has been scaled back for the expected benefit of stopping early, while the costs of the child nodes have not been. 

Presumably patient 100 has not had any claims in a long time (like maybe they died or moved away a couple decade ago), while PostgreSQL thinks their claims will be evenly interspersed through time. 

It is hard to say more than that, without seeing the output of EXPLAIN (ANALYZE) rather than just EXPLAIN.


  Am I missing something obvious - a multiple-index doesn't seem to work - that could help Postgres see a better plan faster?


What multiple-index did you try?  I would think one on sfe_procedure (patient_id, code) would have a reasonable chance to work.

But you can just hit the planner with a big hammer by rewriting it to:

ORDER BY "sfe_claim"."from_date" + interval '0 days' DESC LIMIT 1


This will preclude use of the falsely appealing index "sfe_claim_from_da_3d3cc6_idx"