postgresql sticking "enable_seqscan=False" in a UDF sensible?

Question

I have a query which is taking 40 seconds to execute over the 80ms that it should if postgres where to filter off the primary key rather than doing a sequential scan. I have to force postgres to filter off the index by setting the above flag.

Having to stick this into the UDF seems like quite a hack to me, I mean it's unreasonable IMO that the query planner is chosing a seq scan over a indexed filter. Most of my SQL experience is off SQL Server, and I haven't experienced it missplaning such basic optimizations.

Edit: To anyone interested, the query exeuction time droped from 80ms to 27 ms with 9.2, holly molley.

Answer 1

Do not set it globally, that would lead to terrible query plans. But there are sensible use cases for enable_seqscan=False. I quote the manual here:

It is impossible to suppress sequential scans entirely, but turning this variable off discourages the planner from using one if there are other methods available. The default is on.

So, if for some reason, the planner keeps picking a sequential scan where another type of scan would be faster, this could be your ugly fix - for this query only. If no other route is available, Postgres will still use a sequential scan.

Of course, in most cases you are just covering up the underlying problem - the planner should not be that wrong to begin with. You may want to start digging what's wrong in your setup. Is autovacuum on (default), so ANALYZE is run against your tables automatically? Try a manual

ANALYZE mytable

Or even:

VACUUM FULL ANALYZE

to begin with. Then read the chapter on Planner Cost Constants and search for key words like random_page_cost here or on SO ...
Your settings are probably way off. The packaged defaults are hardly any good for bigger databases.

Answer 2

enable_seqscan=true doesn't do what you think it will. It only allows the planner to choose that. It doesn't force it.

Note it should be on generally anyway. Sequential scans are far less costly in PostgreSQL than in MySQL since they scan in physical order. There are many times when they are the cheaper plan.