From: Justin Pryzby <pryzby@telsasoft.com>
To: Laurent Martelli <martellilaurent@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bad plan
Date: Tue, 23 Jan 2018 09:18:48 -0600
Message-ID: <20180123151848.GC25452@telsasoft.com> (raw)
In-Reply-To: <CAP-_FZFHzrPqtWmtAL0VG9Upo+7UrSobhfnHkwZ4zE1Bkwji5Q@mail.gmail.com>
References: <CAP-_FZFHzrPqtWmtAL0VG9Upo+7UrSobhfnHkwZ4zE1Bkwji5Q@mail.gmail.com>
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
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: pryzby@telsasoft.com, martellilaurent@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Bad plan
In-Reply-To: <20180123151848.GC25452@telsasoft.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