Date: Tue, 23 Jan 2018 09:18:48 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Laurent Martelli <martellilaurent@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bad plan
Message-ID: <20180123151848.GC25452@telsasoft.com>
References: 
 <CAP-_FZFHzrPqtWmtAL0VG9Upo+7UrSobhfnHkwZ4zE1Bkwji5Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAP-_FZFHzrPqtWmtAL0VG9Upo+7UrSobhfnHkwZ4zE1Bkwji5Q@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote:
> Hello all,
> 
> So I have a view, for which I can select all rows in about 3s (returns ~80k
> rows), but if I add a where clause on a column, it takes +300s to return
> the ~8k lines.
> 
> From the plan, I see that it expects to return only 1 row and so choose to
> perform some nested loops. Of course, I did run "ANALYZE", but with no
> success.
> 
> I managed to speed things up with "set enable_nestloop = false;", but is
> that the only choice I have ? Should I report a bug ?


> Here is the default plan :

Can you resend without line breaks or paste a link to explain.depesz?

The problem appears to be here:

->  Nested Loop Left Join  (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359 loops=1)
Join Filter: (sh.share_holder_partner = partner.id)
Rows Removed by Join Filter: 204915707

Justin