Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6673F2E0078 for ; Thu, 14 Feb 2008 19:51:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 73792-08 for ; Thu, 14 Feb 2008 19:51:00 -0400 (AST) Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.182]) by postgresql.org (Postfix) with ESMTP id A7F9D2E0049 for ; Thu, 14 Feb 2008 19:51:00 -0400 (AST) Received: by el-out-1112.google.com with SMTP id r27so632019ele.5 for ; Thu, 14 Feb 2008 15:50:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; bh=uFSWmPDVLdWbxrS2vJGjV3uyECI8nEO6bhb5eUrgFrw=; b=LjDcYJNmJYsvoe2Kis2s5726a59LRtQMlkRLeSXV19qp1ZAy6j+ceeu28izfWJ3CbPPzgb0InpXk4e3jEF3lq4iGVoWfVTZ1SWA5QMVgzSFprh07gw753h4TiP7oc3Cior027SOFVS7K7qy35w2G44qPgsTRu/AMzBvuHA4tl88= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=eAbDktbB6A1QzLPz57o667PRZwlxQ4Y5HOFHEr1L4ENpFimWJMYuqMihMXtvWJKGAlqKBoyw47rMendOLSQnW4tNtHzXKf4Us2KiWrTAEsseRaGpF8fpi+zWYpRgNUW+PmTMufn7gXcBfjdoWrE0fP9w7or6TawTwIXYSP0goig= Received: by 10.142.144.16 with SMTP id r16mr1748047wfd.97.1203033058917; Thu, 14 Feb 2008 15:50:58 -0800 (PST) Received: from ?10.1.1.22? ( [203.217.18.65]) by mx.google.com with ESMTPS id 30sm7637882wfa.2.2008.02.14.15.50.57 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 14 Feb 2008 15:50:58 -0800 (PST) Message-ID: <47B4D338.6020904@gmail.com> Date: Fri, 15 Feb 2008 10:48:08 +1100 From: Chris User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: Thomas Zaksek CC: pgsql-performance@postgresql.org Subject: Re: Join Query Perfomance Issue References: <47B08F19.8000500@ptt.uni-due.de> <47B170BF.5000206@ptt.uni-due.de> <47B41E83.6060509@ptt.uni-due.de> In-Reply-To: <47B41E83.6060509@ptt.uni-due.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/169 X-Sequence-Number: 28711 > Nested Loop (cost=0.00..31157.91 rows=3054 width=14) (actual > time=0.252..149.557 rows=2769 loops=1) > -> Index Scan using > messungen_v_dat_2007_11_12_messpunkt_minute_tag_idx on > messungen_v_dat_2007_11_12 m (cost=0.00..5134.28 rows=3054 width=4) > (actual time=0.085..11.562 rows=2769 loops=1) > Index Cond: ((ganglinientyp = 'M'::bpchar) AND (799 = minute_tag)) > -> Index Scan using messwerte_mv_nr_idx on messwerte_mv p > (cost=0.00..8.51 rows=1 width=18) (actual time=0.031..0.035 rows=1 > loops=2769) > Index Cond: (p.nr = m.messpunkt) > Total runtime: 159.703 ms > (6 rows) > > Nested Loop is not the best regarding to performance, but there isn't a > way to avoid it here? Your own tests have proven it's the right approach for this particular query. > Another strange problem occurs when i retry the query after about 12 > hours break without akivity on the database (starting work in the > morning) : > The query runs incredible slow (~3sec), analyse on the tables doesn't > change much. But when i switch enable_netloop to false, retry the query > (very bad result, > 30sec), then set enable_nestloop back to true, the > query works amazingly fast again (100ms). The o/s has cached some of the data so instead of actually hitting the disk, it's getting it from the o/s cache. -- Postgresql & php tutorials http://www.designmagick.com/