Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 277A364FF2F for ; Fri, 7 Nov 2008 06:45:59 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 80752-06 for ; Fri, 7 Nov 2008 06:45:58 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from pih-relay05.plus.net (pih-relay05.plus.net [212.159.14.18]) by mail.postgresql.org (Postfix) with ESMTP id 3FC7164FF0F for ; Fri, 7 Nov 2008 06:45:56 -0400 (AST) Received: from [84.51.143.99] (helo=server3.office.archonet.com) by pih-relay05.plus.net with esmtp (Exim) id 1KyOqc-0002mp-Tw; Fri, 07 Nov 2008 10:45:51 +0000 Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id B5748274059; Fri, 7 Nov 2008 10:45:50 +0000 (GMT) Message-ID: <49141C5E.2010404@archonet.com> Date: Fri, 07 Nov 2008 10:45:50 +0000 From: Richard Huxton User-Agent: Thunderbird 2.0.0.17 (X11/20080914) MIME-Version: 1.0 To: =?UTF-8?B?0JLRltGC0LDQu9GW0Lkg0KLQuNC80YfQuNGI0LjQvQ==?= CC: pgsql-performance@postgresql.org Subject: Re: PostgreSQL OR performance References: <331e40660811050312u552cab53ifa88e493e1c4f7ac@mail.gmail.com> <1225902700.1432.53.camel@jdavis> <331e40660811060246m51df553egd4e2ba91f08d1434@mail.gmail.com> <29e3942f0811060426k5fe34b1av401a2dba62e4ae3c@mail.gmail.com> <331e40660811060833l49bf837em41ca88d4b2061ea1@mail.gmail.com> <49131ED5.80307@archonet.com> <331e40660811060937q460e6f9fvf336455200d31f56@mail.gmail.com> <331e40660811070114rd70cf0dy194020a9f9ea8802@mail.gmail.com> In-Reply-To: <331e40660811070114rd70cf0dy194020a9f9ea8802@mail.gmail.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Plusnet-Relay: 03c117d815a5243c44984eea92216b1c X-Archive-Number: 200811/49 X-Sequence-Number: 31780 =D0=92=D1=96=D1=82=D0=B0=D0=BB=D1=96=D0=B9 =D0=A2=D0=B8=D0=BC=D1=87=D0=B8= =D1=88=D0=B8=D0=BD wrote: > I am sorry, I've emptied atom_match table, so one part produce 0 result= , but > anyway here is explain: David's right - the total estimate is horribly wrong > "Merge Join (cost=3D518771.07..62884559.80 rows=3D1386158171 width=3D3= 2) (actual > time=3D30292.802..755751.242 rows=3D34749 loops=3D1)" But it's this materialize that's taking the biggest piece of the time. > " -> Materialize (cost=3D469981.13..498937.42 rows=3D2316503 width=3D= 30) > (actual time=3D15915.639..391938.338 rows=3D242752539 loops=3D1)" 15.9 seconds to 391.9 seconds. That's half your time right there. The fact that it's ending up with 242 million rows isn't promising - are you sure the query is doing what you think it is? > " -> Sort (cost=3D469981.13..475772.39 rows=3D2316503 width=3D= 30) (actual > time=3D15915.599..19920.912 rows=3D2316503 loops=3D1)" > " Sort Key: production.company.run_id" > " Sort Method: external merge Disk: 104896kB" By constrast, this on-disk sort of 104MB is comparatively fast. > P.S. May be I've chosen wrong list and my Q better belongs to -hackers? No - hackers is if you want to discuss the code of the database server itself. --=20 Richard Huxton Archonet Ltd