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 AA96FD1B174 for ; Thu, 15 Jul 2004 01:52:51 -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 82100-09 for ; Thu, 15 Jul 2004 04:52:49 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B49ECD1B16E for ; Thu, 15 Jul 2004 01:52:42 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i6F4qcJp016638; Thu, 15 Jul 2004 00:52:40 -0400 (EDT) To: josh@agliodbs.com Cc: "Steinar H. Gunderson" , pgsql-performance@postgresql.org Subject: Re: Odd sorting behaviour In-reply-to: <200407141841.01863.josh@agliodbs.com> References: <20040708101913.GA15871@uio.no> <200407141841.01863.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Wed, 14 Jul 2004 18:41:01 -0700" Date: Thu, 15 Jul 2004 00:52:38 -0400 Message-ID: <16637.1089867158@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/94 X-Sequence-Number: 7485 Josh Berkus writes: >> - The "subquery scan o12" phase outputs 1186 rows, yet 83792 are sorted. > Where >> do the other ~82000 rows come from? > I'm puzzled by the "83792" rows as well. I've a feeling that Explain > Analyze is failing to output a step. No, it's not missing anything. The number being reported here is the number of rows pulled from the plan node --- but this plan node is on the inside of a merge join, and one of the properties of merge join is that it will do partial rescans of its inner input in the presence of equal keys in the outer input. If you have, say, 10 occurrences of "42" in the outer input, then any "42" rows in the inner input have to be rescanned 10 times. EXPLAIN ANALYZE will count each of them as 10 rows returned by the input node. The large multiple here (80-to-one overscan) says that you've got a lot of duplicate values in the outer input. This is generally a good situation to *not* use a mergejoin in ;-). We do have some logic in the planner that attempts to estimate the extra cost involved in such rescanning, but I'm not sure how accurate the cost model is. > Most of your time is spent in that merge join. Why don't you try doubling > sort_mem temporarily to see how it does? Or even raising shared_buffers? Raising shared_buffers seems unlikely to help. I do agree with raising sort_mem --- not so much to make the merge faster as to encourage the thing to try a hash join instead. regards, tom lane