pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: KC ESL <kclesl@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Planner mis-estimation using nested loops followup
Date: Wed, 19 Mar 2008 08:00:47 +0800
Message-ID: <47e057b5.093a360a.0a7d.1ee5@mx.google.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0803181617420.20402@aragorn.flymine.org>
References: <3642025c0803180835n20062314g94870b137d8c3ff7@mail.gmail.com>
	<Pine.LNX.4.64.0803181617420.20402@aragorn.flymine.org>

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 




view thread (7+ messages)

Message-ID: <47e057b5.093a360a.0a7d.1ee5@mx.google.com>
Permalink:  ../47e057b5.093a360a.0a7d.1ee5@mx.google.com/
Also on:    postgresql.org/message-id/47e057b5.093a360a.0a7d.1ee5@mx.google.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: kclesl@gmail.com
  Subject: Re: Planner mis-estimation using nested loops followup
  In-Reply-To: <47e057b5.093a360a.0a7d.1ee5@mx.google.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox