Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id F255C9FA39B for ; Thu, 20 Sep 2007 18:37:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 59649-01 for ; Thu, 20 Sep 2007 18:37:01 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.190]) by postgresql.org (Postfix) with ESMTP id 8D7719FA3D1 for ; Thu, 20 Sep 2007 18:37:01 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id l15so538393rvb for ; Thu, 20 Sep 2007 14:37:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=Wk+tM5wHNOwdDuPtUJou4bzE32OqOWqw/+xaAdVxQ9U=; b=ddG3g+LwEJ+8izF2UcEg0Qj8YwwP6LTKEvjWGgq5FYQdwsPXB7YiBmL5kgL6edH8/wlkp8lvSzLImQz90KriPuTdhOfMjE8QP9SkXkwNQKFTj7sQRNPeA44y0Q77F8lKvSOuAPMYRWg1yy+LOSL/7CIe9/avgHstL8SRr5OEWUY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=p1hs9f9M7C4D65v7bKFvypqll4tn99SiOy28FtInL4mgl2Q59l9WGLn1007J48QqE48jUJkys1tAd6Wy6ZWNKplAsXrj6nnmtmwB8AFHBfjQoIwyK/dbv2UGkXy3kyQSxI3xHJfmWlzQrhqJ95aA9yvgX14tV/Jy5nXc3qV3+JI= Received: by 10.141.99.4 with SMTP id b4mr588827rvm.1190324219925; Thu, 20 Sep 2007 14:36:59 -0700 (PDT) Received: by 10.141.87.4 with HTTP; Thu, 20 Sep 2007 14:36:59 -0700 (PDT) Message-ID: Date: Fri, 21 Sep 2007 00:36:59 +0300 From: "Kamen Stanev" To: pgsql-performance@postgresql.org Subject: query io stats and finding a slow query MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_16082_12974478.1190324219915" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/340 X-Sequence-Number: 26885 ------=_Part_16082_12974478.1190324219915 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi everybody, Is there a way to find which query is doing large io operations and/or which is using cached data and which is reading from disk. I need to see this on a production server to localize a slow and resource eating query. The pg_statio* tables are very handy, but don't help me at all in finding the most disk intensive** query, just the most used table which I already knew. And besides that, please share your experience on how do you decide which queries to optimize and how to reorganize your database? Is there any tools that you use to profile your database. Regards, Kamen Stanev ------=_Part_16082_12974478.1190324219915 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi everybody,

Is there a way to find which query is doing large io operations and/or which is using cached data and which is reading from disk. I need to see this on a production server to localize a slow and resource eating query. The pg_statio* tables are very handy, but don't help me at all in finding the most disk intensive query, just the most used table which I already knew.

And besides that, please share your experience on how do you decide which queries to optimize and how to reorganize your database? Is there any tools that you use to profile your database.

Regards,
Kamen Stanev
------=_Part_16082_12974478.1190324219915--