How to involve table index in filtering of 3 conditions?
You have a problem with correlations of the expressions.  The cases where field3 = 1 are selectively depleted of cases where ((field1 &lt; field2) OR (field2 &lt; 0)).  PostgreSQL thinks that those are independent, but they aren't.  So it thinks you will very quickly find a qualifying row by doing a seq scan.  But instead it had to dig through 4 million before finding the first one.

A quirk of PostgreSQL is that it collects special statistics on functional indexes, but not on plain partial indexes.  So if you add a dummy function to your partial index, you may get a better plan:

create index on mytable (abs(field3)) WHERE (field1 &lt; field2 OR field2 &lt; 0);


And then your query would have to use abs(field3)=1 in the condition.  Of course you can only do that if field3 is never negative, otherwise pick some other dummy function to use.