|
-- run the query
Total runtime: 158941.862 ms alter database film20dev set default_statistics_target=100; analyze; -- run the same query Total runtime: 16049.823 ms Still bad but not as bad. And the mem settings we had were:
work_mem = 1MB # min 64kB maintenance_work_mem = 16MB # min 1MB max_stack_depth = 2MB # min 100kB Changed the work_mem to 16M by: alter database film20dev set work_mem='16MB'; and re-executed the query, but the execution was no different this time: Total runtime: 19555.234 ms Well, that's an improvement by a factor of ten, so we're closer. Got a new explain analyze output from the query with the new stats settings?
You're right, of cours it's a good improvement. I just have an impression that te query can be reworked so that itr runs in ~100ms, so we have lots of optimization to do :)
Here's the new analyze: http://explain-analyze.info/query_plans/4513-recom-by-tags (it takes a bit longer as I executed it from my laptop, not the server) Looking at the query plan it doesn't look like it changed any, just ran faster because you had less data for it to go through. The row estimates are still WAY off. Try setting the default_statistics_target to 1000 (the max), then run analyze, then explain analyze it and see if those nested loops up top that expect 1 to 10 rows but get 1,000s to get closer. If incrementing stats target doesn't help, we may have a corner case. If it was using the proper join methods we could get this down to something in the seconds or less.
I'll check out the code base and look through the part this comes from and see what I can do with the query. The previous example was from my local machine when I had less data.
I ran it on production now and it looks like nothing is actually changing. Even when I increased the value to 1000, it runs faster on the prod server only if the same query is run again. When I change the tag name, it runs equally slow, say: 235178.815 ms. So, it's not the statistics. So, you definitely ran analyze on the database between setting the stats and running the query, right?
Scott, I can give you access to the database dump so that you could test that on your machine. Let me know if you want to examine that issue further.
Resolved with introduction of new recommendations engine (recommendations data now stored in a separate table: core_recommendation) and a reworked query.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Improvement
Closed
Major
Hugely improve performance of filltering recommendations by tags
show default_statistics_target ;
on the db show? Can someone try
alter database <dbname> set default_statistics_target=100;
analyze;
and see if the query runs any faster?
Also, what's the work_mem on that server set to? Often turning it up just a bit from the default of 1M makes a huge difference.
alter database <dbname> set work_mem='16M';