Ygthkb

Rationale to prefer local variables over instance variables?

Reason why dimensional travelling would be restricted

School performs periodic password audits. Is my password compromised?

GPL code private and stolen

is 'sed' thread safe

Is every open circuit a capacitor?

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

Book about a time-travel war fought by computers

How to disable or uninstall iTunes under High Sierra without disabling SIP

Can the Shape Water Cantrip be used to manipulate blood?

Deal the cards to the players

Quitting employee has privileged access to critical information

I've given my players a lot of magic items. Is it reasonable for me to give them harder encounters?

Meaning of word ягоза

How to fix my table, centering of columns

When to use mean vs median

Can a Trickery Domain cleric cast a spell through the Invoke Duplicity clone while inside a Forcecage?

Is there any relevance to Thor getting his hair cut other than comedic value?

Lock enemy's y-axis when using Vector3.MoveTowards to follow the player

When was drinking water recognized as crucial in marathon running?

Relationship between the symmetry number of a molecule as used in rotational spectroscopy and point group

How do I deal with being envious of my own players?

What is the meaning of "notice to quit at once" and "Lotty points”

I can't die. Who am I?

How to replace a sub query using 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

asked 8 mins ago

bunny sunnybunny sunny

61

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

asked 8 mins ago

bunny sunnybunny sunny

61

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

asked 8 mins ago

bunny sunnybunny sunny

61

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

asked 8 mins ago

bunny sunnybunny sunny

61

share

asked 8 mins ago

bunny sunnybunny sunny

61

asked 8 mins ago

bunny sunnybunny sunny

61

asked 8 mins ago

bunny sunnybunny sunny

61