Обсуждение: Query Performance Issue
Hi everyone ,
Have this explain analyze output :
Appreciated for any help .
PG version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
OS version :
CentOS Linux release 7.5.1804 (Core)
shared_buffers : 4GB
work_mem : 8MB
Could you share the query itself please?
And the tables definitions including indexes.
That's not a lot. The 16-batches hash join may have worked faster if you had resources to increase work_mem.work_mem : 8MB
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_main2prm (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
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(). It would be interesting to see how far out the estimate is without the version = 1 clause. If just the recommended_content_id clause is underestimated enough it could be enough to have the planner choose the nested loop. Perhaps upping the stats on that column may help, but it may only help so far as to reduce the chances of a nested loop. If the number of distinct recommended_content_id values is higher than the statistic targets and is skewed enough then there still may be some magic values in there that end up causing a bad plan. It would also be good to know what random_page_cost is set to, and also if effective_cache_size isn't set too high. Increasing random_page_cost would help reduce the chances of this nested loop plan, but it's a pretty global change and could also have a negative effect on other queries. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
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_main2prm (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
Try a pg_hint_plan Rows hint to explore what would happen to the plan if you fixed the bad join cardinality estimate: /*+ rows(prm prc #2028) */ alternatively you could specify a HashJoin hint, but I think it's better to fix the cardinality estimate and then let the optimizer decide what the best plan is. I agree with Justin that it looks like the version and recommended_content_id columns are correlated and that's the likely root cause of the problem, but you don't need to upgrade to fix this one query. ----- Jim Finnerty, AWS, Amazon Aurora PostgreSQL -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
On Sat, Dec 29, 2018 at 1:58 AM David Rowley <david.rowley@2ndquadrant.com> 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().
But "recommended_content_id" and "version" are both in the same table, doesn't that make them base quals?
The most obvious thing to me would be to vacuum product_content_recommendation_main2 to get rid of the massive number of heap fetches. And to analyze everything to make sure the estimation errors are not simply due to out-of-date stats. And to increase work_mem.
It isn't clear we want to get rid of the nested loop, from the info we have to go on the hash join might be even slower yet. Seeing the plan with enable_nestloop=off could help there.
Cheers,
Jeff
On Sat, 29 Dec 2018 at 20:15, Justin Pryzby <pryzby@telsasoft.com> wrote: > On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote: > > 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: I should have checked more carefully. Of course, they are base quals. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services