Thank you for all the leads.
I've increased stats to 1200 on everything obvious (external_id,
attr_name, attr_value, party_id), and ran ANALYZE, but it didn't help at
all - any other ideas of what else could be going wrong ?
We'll disable preparation, but the thing is it works brilliantly 90% of
the time and the other 10% should theoretically be fixable - because
it's almost certainly a border scenario brought on by lack of
maintenance on something somewhere.
Is there any point in trying to rebuild the indexes involved in case
Postgres decided they're too bloated or something like that?
@Shaun: I just finished trying to max out stats and sadly it doesn't
help, thank you very much for trying anyway.
@Tom:
The planner doesn't flip between the plans by itself - it will switch to
the BAD plan at some point and never go back.
The big_table has an extremely uneven distribution indeed. But it still
plans right usually - and this apparently regardless of the statistics
target.
@Jeff: thank you for the clear plan interpretation - but I'm afraid I
don't really understand the second bit:
1) I provided the GOOD plan, so we already know what postgres thinks,
right? (Later edit: guess not. Doesn't work)
2) There's no full table scan in any of the plans - it scans indices,
the problem seems to be that it scans them in the wrong order because it
thinks there are very few WHERE matches in big_table - which is
incorrect, as for that particular pair there is a huge amount of rows.
Thank you,
Andrei