X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A6CD0D1B4BD for ; Fri, 30 Jan 2004 19:19:38 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 68684-05 for ; Fri, 30 Jan 2004 15:19:39 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id D151BD1B4A4 for ; Fri, 30 Jan 2004 15:19:37 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i0UJJQYl017546; Fri, 30 Jan 2004 14:19:26 -0500 (EST) To: David Teran Cc: Stephan Szabo , PgSQL Performance ML Subject: Re: another query optimization question In-reply-to: References: <32DEEB93-534E-11D8-9392-000A95A6F0DC@cluster9.com> <20040130100845.E58632@megazone.bigpanda.com> Comments: In-reply-to David Teran message dated "Fri, 30 Jan 2004 19:20:24 +0100" Date: Fri, 30 Jan 2004 14:19:26 -0500 Message-ID: <17545.1075490366@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/332 X-Sequence-Number: 5506 David Teran writes: > [ query plan ] I got a little distracted by the bizarre actual-time values shown for some of the query steps: > -> Merge Join (cost=2451266.53..2655338.83 rows=13604393 width=8) > (actual time=82899.466..-2371037.726 rows=2091599 loops=1) > -> Sort (cost=2451169.10..2483246.47 rows=12830947 width=8) > (actual time=82891.076..-529619.213 rows=4187378 loops=1) > -> Hash IN Join (cost=507.32..439065.37 rows=12830947 > width=8) (actual time=61.943..1874640.807 rows=4187378 loops=1) The hash-join total time is obviously wrong seeing that the total runtime is only about 100000 msec, and the negative values for the other two are even more obviously wrong. I recall that we saw similar symptoms once before, and I thought we'd fixed it, but I didn't find any relevant mentions in the CVS logs. What version are you running, exactly? Could you repeat the EXPLAIN with client_min_messages set to 'debug2', and see if you see any messages about InstrStartTimer or InstrStopNode? regards, tom lane