PDA

View Full Version : postgresql: enable_seqscan



cirobonano
08-19-2009, 05:53 AM
I was trying to make a custom query and it took ages to complete (I cancelled it after 5 minutes); with some help from #postgresql on irc I discovered that in HM tables seqscan is disabled. Enabling seqscan made the query complete in 8s. Is there some good reason to disable seqscan?

fozzy71
08-19-2009, 06:04 AM
I will forward this to the developer for a reply.

Rvg72
08-20-2009, 07:23 PM
For the majority of queries that HM uses turning off sequence scan leads to an improvement in performance. I suspect that is you did a Vacuum + Full Analyze on your DB then the custom query would work fine with seq scan off. If you are doing some type of query that HM would never perform (where you would want to go through every row in a large table) then leaving it on is probably better for you

Roy

cirobonano
08-21-2009, 10:34 AM
Okay, I understand; this was recommended to me by somebody at #postgresql :

[RhodiumToad] I would recommend with 8GB RAM to set
[RhodiumToad] effective_cache_size=4GB, work_mem=128MB, maintenance_work_mem=512MB, default_statistics_target=100
[RhodiumToad] yeah, it looks like it was having seqscan disabled that slowed it down so much
[RhodiumToad] some people do that as a brute-force solution to planning errors
[RhodiumToad] it's never really recommended
[RhodiumToad] (for exactly this reason, it ends up causing as many planner errors as it fixes)
[RhodiumToad] it's better to tune the planner cost constants instead
[RhodiumToad] having effective_cache_size set too low is a common cause of incorrect use of seqscan
[RhodiumToad] otherwise, increases in cpu_tuple_cost can be an effective way to avoid them without causing the kinds of problems that enable_seqscan=off causes