Anyone using the postgres query optimizer?
Just wondering if anyone has had good results with this tool...
Was it worth the effort?
Exactly what tool are you talking about? Do you mean the analyzer, which uses statistics to automatically tune queries, or the genetic query optimizer, or do you mean the specific settings you can mess with (see http://www.postgresql.org/idocs/inde...me-config.html)?
Or is there another tool I am unaware of?
I'm sorry I was referring to the genetic query optimizer.
As far as I can tell (from my imperfect reading of the docs), the genetic query optimizer is turned on by default. It just does its business in the background. It collects statistics every time you do a VACUUM ANALYZE, or just an ANALYZE on the tables or database, and it uses those statistics to restructure its internal methods of handling your queries.
That link I posted above mentions the various settings that you can play with, in hopes to tweak your query optimization. But, right there in the documentation they say they have no absolute way to tell you what to tweak, and in fact they want you to experiment with the settings, and report your findings. I would say that means for the most part the settings aren't going to be helpful in production. But, I always run a VACUUM ANALYZE at least once a week on my databases; it can't hurt.
I haven't noticed a big performance difference, but then, I'm not working with 50 million rows yet, either .
Thanks for the information, that was BIG help.
I'm one of those idiots addicted to performance tuning, always trying to tweak a setting here and there. I have to tell myself where to stop sometimes. As you know you could spend the rest of your life tuning a database, it never ends.
I think it's good news that the optimizer works in the background, that means less meddling from me!
Also thanks for the other tips, I'll check those out.
June 15th, 2002, 06:59 PM
June 17th, 2002, 01:21 AM
That first one had an really good chapter on VACUUM