Vryjdfkk

I am trying to get twitter data of narendramodi using below command.

SELECT b.t_id,a.profile_image,b.tweet_text,e.media_media_url,b.retweet_count,b.favorite_count as like_count,count(reply_to_status_id) as reply_count,f.imp_count,f.eng_count,f.eng_rate 

FROM twitter_users a LEFT JOIN twitter_tweets b on a.user_id=b.user_id 

LEFT JOIN replies c on b.t_id = c.t_id 

LEFT JOIN media e on b.t_id = e.t_id 

LEFT JOIN metric_aggregates f on f.metric_timestamp= 

(select max(metric_timestamp) FROM twitter_tweet_metric_aggregates g 

WHERE g.t_id=f.t_id and g.t_id=b.t_id) 

WHERE a.twitter_screen_name= 'narendramodi' 

GROUP BY  b.t_id,a.profile_image 

,b.tweet_text,b.retweet_count,b.favorite_count,

e.media_media_url,f.imp_count,f.eng_count,f.eng_rate);

Query was working correctly But, in the above query I have used sub-select to get recent data of imp_counts of each tweet based on timestamp. Because of this sub-select Query_cost was huge and so it was taking more than 15min for query execution. I want to reduce that and should able to execute within 10seconds. For that reason I was trying to use WITH (CTE) expression

WITH metric_counts AS (

SELECT max(metric_timestamp),f.t_id,f.imp_count,f.eng_count,f.eng_rate 

FROM metric_aggregates f LEFT JOIN tweets b on 

f.t_id=b.t_id 

)

SELECT 

b.t_id,a.profile_image,b.tweet_text,e.media_media_url,b.retweet_count

,b.favorite_count as like_count, count(reply_to_status_id) as 

reply_count,metric_counts.imp_count

,metric_counts.eng_count,metric_counts.eng_rate 

FROM twitter_users as a 

LEFT JOIN tweets as b on a.twitter_user_id=b.twitter_user_id 

LEFT JOIN replies c on b.t_id = c.t_id 

LEFT JOIN media e on b.t_id = e.t_id 

LEFT JOIN metric_counts on metric_counts.t_id = b.t_id WHERE 

lower(a.twitter_screen_name)=lower('narendramodi') 

GROUP BY b.t_id,a.profile_image,b.tweet_text,e.media_media_url,

b.retweet_count,b.favorite_count, 

metric_counts.imp_count,metric_counts.eng_count,

metric_counts.eng_rate;

The above WITH expression was giving results of imp_counts also for each tweet but not giving latest record/value. Can anyone help me in achieving this.

Here is the Query cost of WITH query

HashAggregate  (cost=1734856.13..1735618.48 rows=76235 width=673)

So can anyone help me to reduce cost to even lesser but giving results within 15 sec.