Setting enable_seqscan = off in a single SELECT query Setting enable_seqscan = off in a single SELECT query postgresql postgresql

Setting enable_seqscan = off in a single SELECT query


You can use SET LOCAL in your transaction for that. I quote the manual:

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not.

But that's like eating antibiotics when you keep getting sick instead of finding the cause. Normally there is a reason, why the planner picks a suboptimal plan and you should find and fix that. Read more about that in the answer under this related question:
Keep PostgreSQL from sometimes choosing a bad query plan

In particular I suspect that lowering the setting for random_page_cost might be a good idea. The default setting is regularly too conservative (too high). If most or all of your DB is cached (the system cache does that for whatever gets used repeatedly and fits into RAM), random_page_cost can be almost as low (or in extreme cases just as low) as seq_page_cost. random_page_cost is a major factor in calculating the cost of index usage.

And be sure that autovacuum is running and properly configured (takes care of VACUUM and ANALYZE). You need your statistics to be up to date for proper query planning.

And effective_cache_size is regularly set too low out of the box.

Exceptions apply, and sometimes the query planner just doesn't get it, especially with older versions. Which brings me to another delicate point here: upgrade to a more current version of PostgreSQL. The stable release is 9.2. The query planner has been improved quite a bit since Postgres 8.4.