X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1A0A7528A8 for ; Wed, 18 May 2005 11:49:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44764-06 for ; Wed, 18 May 2005 14:49:28 +0000 (GMT) Received: from mail.deg.cc (mail.deg.cc [64.139.134.201]) by svr1.postgresql.org (Postfix) with ESMTP id EA62452896 for ; Wed, 18 May 2005 11:49:27 -0300 (ADT) Received: from deg.cc (unknown [205.242.255.66]) by mail.deg.cc (Postfix) with ESMTP id 59E0C55FEB; Wed, 18 May 2005 10:49:29 -0400 (EDT) Message-ID: <428B55FB.3030805@deg.cc> Date: Wed, 18 May 2005 10:49:31 -0400 From: Pallav Kalva User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.2) Gecko/20040308 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Scott Marlowe Cc: pgsql-admin@postgresql.org Subject: Re: SQL logging (log_min_duration_statement) not working References: <428B4487.8010702@deg.cc> <1116427170.31821.10.camel@state.g2switchworks.com> In-Reply-To: <1116427170.31821.10.camel@state.g2switchworks.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.028 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200505/239 X-Sequence-Number: 17668 Scott Marlowe wrote: >On Wed, 2005-05-18 at 08:35, Pallav Kalva wrote: > > >>Hi Everybody, >> >> >> I am having problems with logging long running SQL in the log file. >>I have set log_min_duration_statement = 5000 (5secs) and it is not >>logging sql statements that took over 5 secs and this is only from >>queries generated by Hibernate . here is a sample query from hibernate : >> >>----------------------------------------------------------------------------------------- >>select listingadd1_.addressid as col_0_0_ >>from listing.listing listing0_, listing.address listingadd1_ >>where ((select count(taxrolls2_.fkaddressid) >> from listing.taxroll taxrolls2_ >> where listingadd1_.addressid=taxrolls2_.fkaddressid)=0 ) >> and(listing0_.fkbestaddressid=listingadd1_.addressid ) >> and(listing0_.entrydate>$1 ) >>order by listingadd1_.addressid asc >>-------------------------------------------------------------------------------------------- >> >> This above query take more than 5 secs to run and it wont get logged , >>where as all other queries which are not generated by Hibernate are >>logged. Is this expected behavior ? am I missing something else ? >>Please advice. >> >> > >Are you sure it's taking more than 5 seconds to run, not that it's >taking <5 seconds to run and >5 seconds to return the data. I'm pretty >sure the time it takes to ship out the data isn't counted in the time >used to decide whether to log the query or not by the postmaster. > >I.e. what does explain analyze QUERY HERE say about the execution time? > > > Hi Scott, Thanks! for the reply. The query for sure runs for more than 5 secs, I passed the same value again and ran the query in the psql with explain analyze . Here is the explain analyze output : explain analyze select listingadd1_.addressid as col_0_0_ from listing.listing listing0_, listing.address listingadd1_ where ((select count(taxrolls2_.fkaddressid) from listing.taxroll taxrolls2_ where listingadd1_.addressid=taxrolls2_.fkaddressid)=0 ) and(listing0_.fkbestaddressid=listingadd1_.addressid ) and(listing0_.entrydate>'2004-12-15' ) order by listingadd1_.addressid asc; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=17503528.41..17503530.10 rows=678 width=4) (actual time=37279.353..37750.532 rows=398459 loops=1) Sort Key: listingadd1_.addressid -> Nested Loop (cost=0.00..17503496.52 rows=678 width=4) (actual time=5.185..31575.629 rows=398459 loops=1) -> Seq Scan on listing listing0_ (cost=0.00..10312.25 rows=135580 width=4) (actual time=4.885..1834.806 rows=406740 loops=1) Filter: (entrydate > '2004-12-15 00:00:00'::timestamp without time zone) -> Index Scan using pk_address_addressid on address listingadd1_ (cost=0.00..129.01 rows=1 width=4) (actual time=0.065..0.067 rows=1 loops=406740) Index Cond: ("outer".fkbestaddressid = listingadd1_.addressid) Filter: ((subplan) = 0) SubPlan -> Aggregate (cost=123.00..123.00 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=406740) -> Index Scan using idx_taxroll_fkaddressid on taxroll taxrolls2_ (cost=0.00..122.89 rows=42 width=4) (actual time=0.009..0.009 rows=0 loops=406740) Index Cond: ($0 = fkaddressid) Total runtime: 38026.606 ms (13 rows)