HeatWare.net header image
HeatWare.net
TwitterRSSEmail
  • Home
  • Windows
    • Windows 8
    • Windows 7
    • Windows Vista
    • Windows XP
  • Linux/Unix
  • Mac
  • Mobile
    • Android
    • iOS
    • Phones (Help / Resources)
  • Software / Programming
    • Free Software
    • Programming – General
    • PHP
    • Ruby/Rails
    • Quality Assurance (QA)
    • Software – General
    • Software Help
    • Databases
  • Cool Websites
  • Other
    • Deals & Bargains
    • News
    • Video Games
    • Hardware
    • Electronics
  • About

3 comments / February 16, 2011 / sood / Databases

How to find (log) slow queries in PostgreSQL 8.x, 9.x

One of the most important factors when troubleshooting the performance of an application or website, is the performance of the database. Improper indexing or inefficient SQL queries can kill the performance of your application rendering it useless. It is amazing what a simple tweak or change to a SQL statement can do. I have seen more than 100x performance improvements just from a one word change in a query. So how you can identify which queries are running slowly in your system? That is easy, just turn on the logging in your PostgreSQL database. This method has been tested in versions 8.3 and 8.4, but should apply to even 9.x!

Enable slow query logging in PostgreSQL

  • Open the file postgresql.conf file in your favorite text editor.
    Note: If you are having trouble finding the file, run the command: find / -name postgresql.conf
  • Look for the line:
    #log_min_duration_statement = -1and replace it with:
    log_min_duration_statement = 100
  • Save the file and reload the PostgreSQL configuration (Don’t worry, it won’t interrupt any active connections)
    service postgresql reload
  • Go to the pg_log folder and open the latest log file. (i.e. /var/lib/pgsql/data/pg_log/)

Setting the value to 100 will log every query that takes 100ms or longer to execute. Depending on the number of slow queries you have, this may be overwhelming, so consider increasing this value.

If you need some assistance in optimizing your slow queries, feel free to post them in the comments section below!

Related Posts

  • MySQL 5.6 - How to log slow queries (Linux)
  • Find PostgreSQL database size using SQL 'Select'
  • pgAdmin - PostgreSQL GUI Management Tool
  • How to install PostgreSQL 8.4 on CentOS 5.5
  • How to backup/dump a PostgreSQL database using pg_dump

Databases PostgreSQL

3 comments… add one
  1. Eyal Elkevity July 18, 2016, 7:07 am

    test

  2. Eyal Elkevity July 18, 2016, 7:09 am

    hey there sorry about the test comment from before I tried to comment like 3 times and it kept erasing my comments.

    anyways, I’m using a mac, and while I was able to find the conf file (with the find command) and update it, I couldn’t find the pg_log dir. do you know where it usually is maybe??

    I did have one finding but it was in the data private folder of pg. I also found another conf file there, and at first tried to edit that one, but then I found another one in a more public folder.

    can anyone help??

    thanks

Cancel reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

  • Why You Should Consider Diving Apps
  • How to Buy a Smartphone that Fits Your Budget
  • How to Overcome Frustrating PDF Stress
  • Convert PDF to Word: Easy, Reliable and Quality Conversion
  • PDF to Excel App ­ – A New Way of Handling Tricky Documents

Recent Comments

  • Valarie Walter on Basic Troubleshooting Steps for your Cell Phone
  • John Mists on A Brief History of Android OS
  • syarif on PostgreSQL: How to reload config settings without restarting database
  • Raghu on How to SSH to a server using Ruby – Part I
  • francisco clemente on Basic Troubleshooting Steps for your Cell Phone

Tags

ACSLS Android Bargain Cell Phones Cool Software Cool Websites Databases Deals Ebooks Facebook Free Software G2x Galaxy S5 iOS iPhone Kindle LG Linux Linux/Unix Mac Mobile mysql Office OpenStack OS X PHP Postgres PostgreSQL ruby Samsung Galaxy S6 Shell Smartphones Sun T-Mobile Tips Tips & Tricks Ubuntu Unix Virtualization VMWare Windows Windows 7 Windows 8 Windows Vista Windows XP

Latest Tweet

Follow @HeatwaredotNet

SP
@HeatwaredotNet

  • Why You Should Consider Diving Apps https://t.co/Is41cdUv2I #diving-apps
    about 6 years ago

All Categories

Other Links

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2015 — HeatWare