Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E255E64FC21 for ; Sat, 15 Nov 2008 09:57:09 -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 25806-01 for ; Sat, 15 Nov 2008 09:57:07 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f13.google.com (mail-gx0-f13.google.com [209.85.217.13]) by mail.postgresql.org (Postfix) with ESMTP id 35EA064FC1E for ; Sat, 15 Nov 2008 09:57:06 -0400 (AST) Received: by gxk6 with SMTP id 6so520726gxk.19 for ; Sat, 15 Nov 2008 05:57:04 -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:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=CdzgapM9Bk/0fw5KiCUBwTu4ryro1OqhhZGscXESnK4=; b=SJ4g6A4AGPnqDiTqFBdmilSoLc/ars4+LEbD2l0lrOcGTsvPbvl61mqw6nMSN3bhqd mBDQH5MXVVoG/Fj1JdDwQhcpnEvpvWlE2E0kPJMT6ZrZlC6xbDmwVWadL0gsRsBg5NOG 1ChJWTv6sbrprXGcZN5gqcCYb74waew/MX1GQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=x1cJLGTPb4YBRxvN8mJac1xXILIoiweIQnnOe1Mbo/e2lbP+VQYrZZL26Dc3GeqzLx /wFWoGya89yX4d56W8DBILuc8bRWO3e0O0QDMFGGMT8UKJAh55tmYhvrcx3kQwtXVD+c R+p0s5MkFDeiNNw1rSXOCtPRX6NFhy23HaW3c= Received: by 10.65.188.14 with SMTP id q14mr2018771qbp.56.1226757424535; Sat, 15 Nov 2008 05:57:04 -0800 (PST) Received: by 10.64.142.18 with HTTP; Sat, 15 Nov 2008 05:57:03 -0800 (PST) Message-ID: <331e40660811150557s50e54c54m974645e31e30a612@mail.gmail.com> Date: Sat, 15 Nov 2008 15:57:03 +0200 From: "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" To: "Richard Huxton" Subject: Re: PostgreSQL OR performance Cc: pgsql-performance@postgresql.org In-Reply-To: <49141C5E.2010404@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_37651_5046983.1226757424547" 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> <49141C5E.2010404@archonet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200811/138 X-Sequence-Number: 31869 ------=_Part_37651_5046983.1226757424547 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 2008/11/7 Richard Huxton > But it's this materialize that's taking the biggest piece of the time. > > > " -> Materialize (cost=469981.13..498937.42 rows=2316503 width=30) > > (actual time=15915.639..391938.338 rows=242752539 loops=1)" > > 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? I am not. I can't see how materialize can multiply number of rows it gets from sort by 100. > > > " -> Sort (cost=469981.13..475772.39 rows=2316503 width=30) > (actual > > time=15915.599..19920.912 rows=2316503 loops=1)" > > " Sort Key: production.company.run_id" > > " Sort Method: external merge Disk: 104896kB" > > By constrast, this on-disk sort of 104MB is comparatively fast. > ------=_Part_37651_5046983.1226757424547 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

2008/11/7 Richard Huxton <dev@archonet.com>
But it's this materialize that's taking the biggest piece of the time.

> "  ->  Materialize  (cost=469981.13..498937.42 rows=2316503 width=30)
> (actual time=15915.639..391938.338 rows=242752539 loops=1)"

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?

I am not. I can't see how materialize can multiply number of rows it gets from sort by 100.



> "        ->  Sort  (cost=469981.13..475772.39 rows=2316503 width=30) (actual
> time=15915.599..19920.912 rows=2316503 loops=1)"
> "              Sort Key: production.company.run_id"
> "              Sort Method:  external merge  Disk: 104896kB"

By constrast, this on-disk sort of 104MB is comparatively fast.

------=_Part_37651_5046983.1226757424547--