I am working in query tuning and decided to go for partitioning my tables. There are 3 tables in the base query and only one table makes the query slower. I tried to create partitions but that table is holding only ids referencing other two tables. The remaining two tables partitioned based on date and another on category. Whenever the partitioned table is joined with 3 tables, the query is too slow. It has 20M rows (this table will be updated every week for new data - so it's growing bigger).

This is the sample query:

select t.id as topic_id, t.value as value, t.topic as topic, 
       COUNT(pt1.topic_id) as count  
 from posts_test p --partitioned table---
 join post_locations_test plt on plt.post_id = p.id ---partitioned table---
 join post_topics pt on pt.post_id = plt.post_id -- not partitioned --
 join post_topics pt1 on pt.post_id = pt1.post_id -- not partitioned--
 JOIN topics_test t on pt1.topic_id = t.id -- partitioned---
 where p.date_posted BETWEEN ('2019-07-15'::date - interval '6 month') AND '2019-07-15'::date 
   and plt.country_code = 'GB' 
   and pt.topic_id = 'a451d3e2-f593-4b39-8f4b-d0a97422a344' 
group by t.id,pt1.topic_id  

So in the above query posts_test is having post_id and topics table is having topic_id. post_topics table is holding both post_id and topic_id where this table references the other tables. Because post_topics is mapped by topic_id - each post is mapped with multiple topics. This is where I am finding myself in difficulty at optimizing the join.

posts has nearly 8 million rows and topics_id has 8k. So either way I can't create partitions for these. I am lost on optimizing this table.

enter image description here

Could you please suggest what I can do to improve the join or how could we create partitions in post_topics table - just having ids?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.