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 ORDER BY count DESC;
So in the above query
posts_test is having
topics table is having
post_topics table is holding both
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.
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?