agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Justin Pryzby <pryzby@telsasoft.com>
To: neslişah demirci <neslisah.demirci@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Performance Issue
Date: Fri, 28 Dec 2018 09:32:05 -0600
Message-ID: <20181228153205.GM30382@telsasoft.com> (raw)
In-Reply-To: <CABFxtPedz4zL+aPWut4+=um4av1aAXr6OVRfRB_6K7mJKMbEcw@mail.gmail.com>
References: <CABFxtPedz4zL+aPWut4+=um4av1aAXr6OVRfRB_6K7mJKMbEcw@mail.gmail.com>
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>*
Row counts are being badly underestimated leading to nested loop joins:
|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)
Apparently, recommended_content_id and version aren't independent condition,
but postgres thinks they are.
Would you send statistics about those tables ? MCVs, ndistinct, etc.
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram
I think the solution is to upgrade (at least) to PG10 and CREATE STATISTICS
(dependencies).
https://www.postgresql.org/docs/10/catalog-pg-statistic-ext.html
https://www.postgresql.org/docs/10/sql-createstatistics.html
https://www.postgresql.org/docs/10/planner-stats.html#PLANNER-STATS-EXTENDED
https://www.postgresql.org/docs/10/multivariate-statistics-examples.html
Justin
view thread (57+ messages) latest in thread
Message-ID: <20181228153205.GM30382@telsasoft.com>
Permalink: ../20181228153205.GM30382@telsasoft.com/
Also on: postgresql.org/message-id/20181228153205.GM30382@telsasoft.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: pryzby@telsasoft.com, neslisah.demirci@gmail.com
Subject: Re: Query Performance Issue
In-Reply-To: <20181228153205.GM30382@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox