Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Try EXPLAIN ANALYZE on the misplanned query. See Using EXPLAIN for tips. Basic planning mistakes are generally due to non-existent statistics. –  Daniel Vérité Sep 11 '12 at 21:31
add comment

2 Answers 2

up vote 1 down vote accepted

There are sensible use cases for enable_seqscan=False.

enable_seqscan plays a special role. 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. 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.

share|improve this answer
add comment

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.

share|improve this answer
    
aah crap I made a mistake, I meant to disable seqscans. –  Hassan Syed Sep 11 '12 at 16:06
    
In that case, sure, but it is probably better to try to find and address the underlying problem. Maybe it is insufficient or outdated stats? Maybe it is a complex join and GEQO is picking the wrong tables to analyze (in that case tweak your GEQO settings). Yes, you can do this. The big cost of doing so though is that if you start micromanaging the planner, then your data changes and suddenly you have bigger problems. –  Chris Travers Sep 12 '12 at 0:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.