[keslerm]

You can push it in favor of certain options by disabling the one you don't want, such as sequential scan.

SET enable_seqscan = OFF;

We use these options a lot on tables that result in odd query plans to get them doing the best option..

[btilly]

That's a random sledgehammer, but that is how I have been solving the problem. I've set enable_seqscan, enable_nestloop and enable_material to false and it is working at the moment. At first I only turned off enable_seqscan, but then I turned off the other two after we switched database hosts and the query went belly up.

What scares me is that this is unreliable, and according to the documentation, the optimizer is free to choose to ignore everything that I say whenever it wants. The fact that it already HAS done that to me does not provide me comfort.

[Jweb_Guru]

Disabling sequential scans is generally reliable. It sets the planner cost of seq_scan excessively high, so the planner will only choose it if there's no other choice (at least, that's been my experience). Of course, if your query is complex enough to trigger GEQO that might not work out.