Setting enable_seqscan = off in a single SELECT query

Question

After several tests on one of my queries (Postgres) I realized that by setting enable_seqscan = off, the query takes 1/3 of its original time (done using the psql console and EXPLAIN ANALYZE)

Since it is not recommendable to change this setting for the whole server, I want to set it to OFF only for this query.
How can I do it? Is it possible?

My implementation is based on framework Kohana (PHP), which uses a DB object ( DB::select ) to execute the query.

My postgres is 8.4.9 on a CentOS Linux.

Solution

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. You should find and fix that. See:

• Keep PostgreSQL from sometimes choosing a bad query plan

I suspect a lower setting for random_page_cost might work for you. 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.

Be sure that autovacuum is running and properly configured (takes care of VACUUM and ANALYZE). You need column 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 current version of PostgreSQL. Yours is out of date.

Comments

[thomasfuchs]

In real-world situations, where your users suffer, you don't always have the luxury of doing full-on investigations or restarting your database whenever you like. At least Postgres should provide index hinting like every other major RDBMS, so you can make things work while you work on a better permanent solution.