Sent from my iPhone
Oh dear! Where to even begin? There is no way to suggest possible solutions without knowing a lot more about how things are currently configured and what, exactly, about your application is slow. Just to address your particular suggestions, increasing the default statistics target would only help if an explain analyze for a slow query indicates that the query planner is using inaccurate row count estimates for one or more steps in a query plan. Depending upon the frequency of this problem it may be better to increase statistics target just for individual columns rather than across the entire db cluster. Setting enable_seqscan to off is almost never a good solution to a problem, especially db-wide. If the planner is selecting a sequential scan when an alternative strategy would perform much better, then it is doing so because your configuration is not telling the query planner accurate values for the cost of sequential access vs random access - or else the statistics are inaccurate causing it to select a seq scan because it thinks it will traverse more rows than it actually will. In short, you need to read a lot more about performance tuning Postgres rather than taking stab-in-the-dark guesses for solutions. I believe it was pointed out that at least one query that is problematic for you is filtering based on the value of individual indexes of an array column - which means you actually need break those values into separate columns with indexes on them or create an index on column[x] so that the planner can use that. But if the problem is general slowness across your whole app, it is possible that the way your app uses the db access API is inefficient or you may have a misconfiguration that causes all db access to be slow. Depending on your hardware and platform, using the default configuration will result in db performance that is far from optimal. The default config is pretty much a minimal config. I'd suggest you spend at least a day or two reading up on Postgres performance tuning and investigating your particular problems. You may make quite a bit of improvement without our help and you'll be much more knowledgable about your db installation when you are done. At the very least, please look at the mailing list page on the Postgres website and read the links about how to ask performance questions so that you at least provide the list with enough information about your problems that others can offer useful feedback. I'd provide a link, but I'm on a phone. --sam
|
