Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9D78E2E00D5 for ; Tue, 18 Mar 2008 21:01:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 25418-02 for ; Tue, 18 Mar 2008 21:00:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.228]) by postgresql.org (Postfix) with ESMTP id E0E472E00D7 for ; Tue, 18 Mar 2008 21:00:55 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 76so179311wra.10 for ; Tue, 18 Mar 2008 17:00:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:x-mailer:date:to:from:subject:in-reply-to:references:mime-version:content-type:message-id; bh=qoXXpARZcqnBArb49l4chIWRtR9LDtCTux/BQ+9gSWU=; b=g+1ssgzua7oyibtARagpUOt0EM1n8tvbnUbVUNW/0ksssjwihtfcLR9E9JZXxCLN8QyHkoNxCrYEoUXjx6QpObfzG8bZmvZtgsIDSk5kIFLqQ2ogctsPGGBpmS+tJgr3ie2Xw06qbmqLBXNEuS9v5xUuOAG/Wo0ytDRHEEtzcp0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=x-mailer:date:to:from:subject:in-reply-to:references:mime-version:content-type:message-id; b=Qe2IOGKi1Bnt9IVhCBuezkmzjuFWZ7ls4/dpE9HoBd0O+IEz0+HpDN8sSD/owT+hovlUycp7czRHySYRhHC1wT3Wn7HanA81BQvpWX1m6rQlyi3tu4LjfR5Q/imKZnzQNHfdZx+qxQ0DknP8wMSFAr3fPK8sOLysr9pfxgE6vCY= Received: by 10.151.101.20 with SMTP id d20mr1368886ybm.193.1205884854945; Tue, 18 Mar 2008 17:00:54 -0700 (PDT) Received: from n2.gmail.com ( [124.244.176.100]) by mx.google.com with ESMTPS id g9sm20621924wra.10.2008.03.18.17.00.52 (version=SSLv3 cipher=OTHER); Tue, 18 Mar 2008 17:00:53 -0700 (PDT) X-Mailer: QUALCOMM Windows Eudora Version 7.1.0.9 Date: Wed, 19 Mar 2008 08:00:47 +0800 To: pgsql-performance@postgresql.org From: KC ESL Subject: Re: Planner mis-estimation using nested loops followup In-Reply-To: References: <3642025c0803180835n20062314g94870b137d8c3ff7@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Message-ID: <47e057b5.093a360a.0a7d.1ee5@mx.google.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200803/387 X-Sequence-Number: 29341 At 00:24 08/03/19, Matthew wrote: >On Tue, 18 Mar 2008, Chris Kratz wrote: >>In moderately complex to very complex ad hoc queries in our system, >>we were consistently having the system massively underestimate the >>number of rows coming out of join at a low level making these >>queries very slow and inefficient. > >I have long thought that perhaps Postgres should be a little more >cautious about its estimates, and assume the worst case scenario >sometimes, rather than blindly following the estimates from the >statistics. The problem is that Postgres uses the statistics to >generate best estimates of the cost. However, it does not take into >account the consequences of being wrong. If it was more clever, then >it may be able to decide to use a non-optimal algorithm according to >the best estimate, if the optimal algorithm has the possibility of >blowing up to 1000 times the work if the estimates are off by a bit. > >Such cleverness would be very cool, but (I understand) a lot of >work. It would hopefully solve this problem. > >Matthew Just a crazy thought. If Postgres could check its own estimates or set some limits while executing the query and, if it found that the estimates were way off, fall back to a less optimal plan immediately or the next time, that would be cool. KC