Date: Sat, 29 Dec 2018 01:15:35 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: =?utf-8?B?bmVzbGnFn2Fo?= demirci <neslisah.demirci@gmail.com>,
	David Rowley <david.rowley@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Performance Issue
Message-ID: <20181229071535.GX30382@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAKJS1f8933Q2z7dugFcWoWSFAF3deKLMC=98QP_SXT_qPF3J9w@mail.gmail.com>
 <CABFxtPedz4zL+aPWut4+=um4av1aAXr6OVRfRB_6K7mJKMbEcw@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Thu, Dec 27, 2018 at 10:25:47PM +0300, neslişah demirci wrote:
> Have this explain analyze output :
> 
> *https://explain.depesz.com/s/Pra8a <https://explain.depesz.com/s/Pra8a>*

On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote:
> On Sat, 29 Dec 2018 at 04:32, Justin Pryzby <pryzby@telsasoft.com> wrote:
> > I think the solution is to upgrade (at least) to PG10 and CREATE STATISTICS
> > (dependencies).
> 
> Unfortunately, I don't think that'll help this situation. Extended
> statistics are currently only handled for base quals, not join quals.
> See dependency_is_compatible_clause().

Right, understand.

Corrrect me if I'm wrong though, but I think the first major misestimate is in
the scan, not the join:

|Index Scan using product_content_recommendation_main2_recommended_content_id_idx on product_content_recommendation_main2 prm (cost=0.57..2,031.03 rows=345 width=8) (actual time=0.098..68.314 rows=3,347 loops=1)
|Index Cond: (recommended_content_id = 3371132)
|Filter: (version = 1)
|Rows Removed by Filter: 2708

Justin