X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4B947D7D0A for ; Sat, 27 Aug 2005 11:26:59 -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 45407-09 for ; Sat, 27 Aug 2005 14:26:58 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 96F8DD7D04 for ; Sat, 27 Aug 2005 11:26:56 -0300 (ADT) 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 j7RER19s001117; Sat, 27 Aug 2005 10:27:01 -0400 (EDT) To: Arjen van der Meijden Cc: pgsql-performance@postgresql.org Subject: Re: Inefficient queryplan for query with intersectable In-reply-to: <4310456F.8040303@tweakers.net> References: <430EF844.70905@tweakers.net> <430F1393.5040704@archonet.com> <430F8699.3030400@tweakers.net> <26436.1125096976@sss.pgh.pa.us> <4310456F.8040303@tweakers.net> Comments: In-reply-to Arjen van der Meijden message dated "Sat, 27 Aug 2005 12:50:23 +0200" Date: Sat, 27 Aug 2005 10:27:01 -0400 Message-ID: <1116.1125152821@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/477 X-Sequence-Number: 14224 Arjen van der Meijden writes: > But appareantly there is a bug in the explain mechanism of the 8.1devel > I'm using (I downloaded a nightly 25 august somewhere in the morning > (CEST)), since it returned: > ERROR: bogus varno: 9 Yeah, someone else sent in a test case for this failure (or at least one with a similar symptom) yesterday. I'll try to fix it today. > Is a nested loop normally so much (3x) more costly than a hash join? Or > is it just this query that gets estimated wronly? There's been some discussion that we are overestimating the cost of nestloops in general, because we don't take into account that successive scans of the inner relation are likely to find many pages already in cache from the earlier scans. So far no one's come up with a good cost model to use for this, though. regards, tom lane