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 DA88153379 for ; Fri, 6 May 2005 17:35:30 -0300 (ADT) 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 89990-06 for ; Fri, 6 May 2005 20:35:28 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.199]) by svr1.postgresql.org (Postfix) with ESMTP id C223952A62 for ; Fri, 6 May 2005 17:35:28 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 68so233744wri for ; Fri, 06 May 2005 13:35:30 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=rMDQ5ur5u03VR1323qgCccny1BzZr/waSc4fDjG/77yJ+73gpF2RywbYytoWxi7f6bkmGQCK+FrTycpxeMJkp2h0FFL9Rs5wmsIvxn28WUQ7Z1rH3eiq6cm4L3w6n/atGO38R9p+dYqC9675PVfvS7816562ve1q9Gm2GyyS79o= Received: by 10.54.84.2 with SMTP id h2mr1056364wrb; Fri, 06 May 2005 13:35:30 -0700 (PDT) Received: by 10.54.99.14 with HTTP; Fri, 6 May 2005 13:35:30 -0700 (PDT) Message-ID: <37d451f7050506133534923deb@mail.gmail.com> Date: Fri, 6 May 2005 15:35:30 -0500 From: Rosser Schwarz Reply-To: Rosser Schwarz To: Derek Buttineau|Compu-SOLVE Subject: Re: [SQL] ORDER BY Optimization Cc: pgsql-sql@postgresql.org, pgsql-performance@postgresql.org In-Reply-To: <427BCA10.4090208@csolve.net> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <427BCA10.4090208@csolve.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.28 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200505/63 X-Sequence-Number: 12227 while you weren't looking, Derek Buttineau|Compu-SOLVE wrote: > I'm hoping this is the right place to send this. The PostgreSQL Performance list, pgsql-performance@postgresql.org would be more appropriate. I'm copying my followup there, as well. As for your query, almost all the time is actually spent in the nestloop, not the sort. Compare: > -> Sort (cost=3D31402.85..31405.06 rows=3D886 width=3D306) (actual > time=3D87454.187..87454.240 rows=3D10 loops=3D1) vs. > -> Nested Loop (cost=3D0.00..31359.47 rows=3D886 width=3D306) > (actual time=3D4.740..86430.468 rows=3D26308 loops=3D1) That's 50-ish ms versus 80-odd seconds. It seems to me a merge join might be more appropriate here than a nestloop. What's your work_mem set at? Off-the-cuff numbers show the dataset weighing in the sub-ten mbyte range. Provided it's not already at least that big, and you don't want to up it permanently, try saying: SET work_mem =3D 10240; -- 10 mbytes immediately before running this query (uncached, of course) and see what happens. Also, your row-count estimates look pretty off-base. When were these tables last VACUUMed or ANALYZEd? /rls --=20 :wq