X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 69C3653AE4 for ; Thu, 7 Apr 2005 17:13:59 +0100 (BST) 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 24630-08 for ; Thu, 7 Apr 2005 16:13:50 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9DAD453ACF for ; Thu, 7 Apr 2005 17:13:50 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j37GDmu8003394; Thu, 7 Apr 2005 12:13:48 -0400 (EDT) To: John Arbash Meinel Cc: Joel Fradkin , PostgreSQL Perform Subject: Re: Any way to speed this up? In-reply-to: <42555396.40303@arbash-meinel.com> References: <005301c53b84$6b83f8a0$797ba8c0@jfradkin> <42555396.40303@arbash-meinel.com> Comments: In-reply-to John Arbash Meinel message dated "Thu, 07 Apr 2005 10:36:54 -0500" Date: Thu, 07 Apr 2005 12:13:48 -0400 Message-ID: <3393.1112890428@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/164 X-Sequence-Number: 11561 John Arbash Meinel writes: >> " -> Sort (cost=59478.03..59909.58 rows=172618 width=75) (actual >> time=46844.000..46985.000 rows=159960 loops=1)" >> >> " Sort Key: a.locationid" >> > This sort actually isn't taking very long. It starts at 46800 and runs > until 47000 so it takes < 1 second. >> " -> Merge Right Join (cost=0.00..39739.84 rows=172618 >> width=75) (actual time=250.000..43657.000 rows=176431 loops=1)" You're not reading it quite right. The first "actual" number is the time at which the first result row was delivered, which for a sort is after the completion of (the bulk of) the sorting work. What you really need to look at is the difference between the completion times of the node and its immediate input(s). In this case I'd blame the sort for 46985.000 - 43657.000 msec. Come to think of it, though, you should not be putting a whole lot of trust in EXPLAIN ANALYZE numbers taken on Windows, because they are based on gettimeofday which has absolutely awful resolution on that platform. (There's a workaround for this in our CVS, but it's not in 8.0.*.) I think we can still conclude that the indexscan on tblassociate is most of the cost, but I wouldn't venture to say that it's exactly such-and-such percent. regards, tom lane