Sfrgttk

An Undercover Army

Meaning of '4:1 (3:0)' as score in football (World Cup match)

If nine coins are tossed, what is the probability that the number of heads is even?

Are there other characters in the Star Wars universe who had damaged bodies and needed to wear an outfit like Darth Vader?

Is divide-by-zero a security vulnerability?

Ahoy, Ye Traveler!

How to mitigate "bandwagon attacking" from players?

How can neutral atoms have exactly zero electric field when there is a difference in the positions of the charges?

What is a term for a function that when called repeatedly, has the same effect as calling once?

The need of reserving one's ability in job interviews

How can I conditionally format my HTML table?

PTIJ: Why can't I sing about soda on certain days?

How can I be pwned if I'm not registered on the compromised site?

What is better: yes / no radio, or simple checkbox?

Wardrobe above a wall with fuse boxes

Misplaced tyre lever - alternatives?

Is there a frame of reference in which I was born before I was conceived?

Practical reasons to have both a large police force and bounty hunting network?

function only contains jump discontinuity but is not piecewise continuous

PTIJ: Is all laundering forbidden during the 9 days?

Why do phishing e-mails use faked e-mail addresses instead of the real one?

Why are special aircraft used for the carriers in the United States Navy?

Did Amazon pay $0 in taxes last year?

How to fix my table, centering of columns

How to make use of WITH in PostgreSQL to reduce query cost

join table or cteNo advantage of using Cross Apply or CTE over inline sub-queryimprove performance with schema design, a lot joinRow number with reset in PostgreSQLproblem query postgresqlUpdating column with multiple sub-queriesPostgres update with replaceHow to optimize PostgreSQL query with multiple joins and subqueries and slow grouping/sortingHow can I get performance using PostgreSQL CTE recursive?Using WITH clause with INSERT statement in POSTGRESQL

0

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.

postgresql-9.4 cte

share|improve this question

edited 2 mins ago

bunny sunny

asked 3 hours ago

bunny sunnybunny sunny

62

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please edit your question and add the execution plan generated using explain (analyze, buffers) (not just a "simple" explain) as formatted text, no screen shots please. Or upload the plan to explain.depesz.com
  
  – a_horse_with_no_name
  1 hour ago
  

  
  
  
  

add a comment | 

0

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.

postgresql-9.4 cte

share|improve this question

edited 2 mins ago

bunny sunny

asked 3 hours ago

bunny sunnybunny sunny

62

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please edit your question and add the execution plan generated using explain (analyze, buffers) (not just a "simple" explain) as formatted text, no screen shots please. Or upload the plan to explain.depesz.com
  
  – a_horse_with_no_name
  1 hour ago
  

  
  
  
  

add a comment | 

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.

postgresql-9.4 cte

share|improve this question

edited 2 mins ago

bunny sunny

asked 3 hours ago

bunny sunnybunny sunny

62

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);

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;

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

share|improve this question

edited 2 mins ago

bunny sunny

asked 3 hours ago

bunny sunnybunny sunny

62

share|improve this question

edited 2 mins ago

bunny sunny

asked 3 hours ago

bunny sunnybunny sunny

62

asked 3 hours ago

bunny sunnybunny sunny

62

asked 3 hours ago

bunny sunnybunny sunny

62