#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Question Anyone using the postgres query optimizer?


    Just wondering if anyone has had good results with this tool...

    Was it worth the effort?
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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?
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13
    I'm sorry I was referring to the genetic query optimizer.
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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 .
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Thumbs up


    rycamor,

    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.
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    New article on PostgreSQL performance tuning: http://www.argudo.org/postgresql/soft-tuning.html

    Also, check out Bruce Momjian's article on hardware-related performance tuning: http://www.ca.postgresql.org/docs/mo...w_performance/
    Last edited by rycamor; June 15th, 2002 at 07:13 PM.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Thumbs up


    Good article.

    That first one had an really good chapter on VACUUM

IMN logo majestic logo threadwatch logo seochat tools logo