Re: Advise needed for a join query with a where conditional

Note that the inner query is fast (takes only about 0.5 second):

SELECT "fb_ad_groups"."id" FROM "fb_ad_groups" WHERE "fb_ad_groups"."id" IN
(SELECT "fb_ad_groups"."id" FROM "fb_ad_groups" INNER JOIN
"custom_tags_fb_ad_groups" ON "fb_ad_groups"."id" =
"custom_tags_fb_ad_groups"."fb_ad_group_id" INNER JOIN "custom_tags" ON
"custom_tags_fb_ad_groups"."custom_tag_id" = "custom_tags"."id" WHERE
"custom_tags"."custom_tag_group_id" = 235 AND "fb_ad_groups"."ad_account_id"
= 29) AND "fb_ad_groups"."fb_ad_set_id" IN (166302, 39917, 78123, 194477,
145218, 177579, 89732, 177674, 135464, 34510, 167214, 193144, 173264,
168117, 140425, 169398, 146744, 170529, 183603, 173473, 88015, 117229,
50056, 135148, 116862, 169811, 57620, 159177, 57850, 177677, 127638, 187933,
167885, 73687, 191732, 135058, 186625, 156565, 135150, 164615, 67089,
146341, 168521, 183634, 138699, 165182, 156675, 134834, 169774, 152209,
67048, 146801, 75084, 165749, 70322, 152206, 143700, 139109, 169659, 117316,
134473, 152105, 94276, 163671, 162461, 41502, 75087, 45153, 184190, 185004,
95300, 160507, 189382, 135461, 169085, 117485, 166205, 57572, 132230,
187890, 44333, 185096, 45154, 149021, 177116, 177580, 160509, 89730, 50007,
161811, 164414, 166207, 148633, 166786, 78189, 185745, 169564, 185682,
60898, 165107, 165180, 190722, 175737, 89694, 140764, 148253, 173610,
139106, 162788, 174475, 168806, 135151, 166542, 185826, 173472, 56723,
167017, 177354, 191734, 185618, 169808, 95798, 135640, 143698, 145272,
57616, 126137, 171387, 146690, 185843, 169396, 140427, 35003, 44844, 179589,
50061, 67091, 167015, 162021, 159175, 185842, 169292, 152208, 34909, 161913,
152107, 165337, 67057, 159277, 134998, 166545, 166784, 60895, 135462, 97173,
162133, 191615, 78188, 117226, 191735, 50069, 191617, 185007, 140496,
134835, 173263, 162018, 121551, 174474, 67058, 95797, 148737, 140326,
162791, 34547, 140323, 136350, 34752, 97177, 160609, 57625, 140590, 172303,
175740, 34932, 67086, 136222, 135063, 179591, 117561, 139140, 194476,
175738, 34492, 161345, 136468, 177151, 94274, 166923, 34977, 162240, 167216,
169563, 121073, 117478, 138086, 135231, 165104, 34755, 148635, 117313,
67051, 127197, 127478, 34826, 162132, 132229, 191616, 164928, 116859,
171386, 117318, 185038, 148302, 173475, 50082, 148250, 148252, 84923,
170990, 176730, 182258, 162243, 161910, 34904, 166671, 148734, 163570,
165336, 126134, 148736, 67055, 45148, 173613, 190906, 34589, 89733, 161243,
184975, 152207, 145271, 117314, 140251, 194499, 190721, 185620, 169562,
170987, 169087, 139143, 140253, 177675, 140147, 177759, 143540, 169293,
183635, 185034, 116911, 185033, 170989, 165105, 170908, 185063, 177121,
75085, 132263, 185720, 164415, 185037, 45119, 177154, 68771, 135573, 159174,
169807, 166785, 161808, 145273, 147682, 34601, 120501, 35093, 140116,
169812, 70323, 175902, 191644, 175981, 117483, 185744, 186290, 134870,
179546, 161246, 134832, 121549, 164929, 183651, 178933, 176063, 162241,
146803, 78121, 34936, 120500, 135062, 187934, 151896, 166300, 34564, 185810,
117315, 190652, 135641, 117279, 158630, 159278, 95299, 166920, 34570,
175654, 134863, 95275, 140150, 68769, 127633, 95274, 140591, 60897, 167887,
185098, 116861, 132419, 117559, 133865, 173265, 126601, 39693, 162350,
177153, 168630, 127578, 158398, 136219, 152382, 126605, 34310, 167217,
169658, 169294, 135059, 140372, 134864, 148303, 95778, 173612, 84920,
177357, 135229, 174488, 185586, 166762, 193145, 167016, 132265, 88016,
169809, 126132, 165825, 166763, 39698, 138810, 177256, 134866, 95800,
146691, 149284, 174486, 173611, 169295, 151788, 151789, 34573, 178642,
185619, 162353, 178701, 190903, 151895, 117484, 169773, 117479, 160153,
164617, 138764, 133970, 127474, 132418, 182257, 174487, 178511, 89731,
121072, 50040, 134750, 165258, 165905, 133923, 173694, 117477, 185683,
136220, 166206, 141678, 185660, 133922, 177578, 185066, 139141, 185662,
183602, 134751, 158397, 78412, 84925, 177117, 156566, 185064, 146339,
126609, 44291, 140250, 166702, 160611, 190651, 132421, 156672, 167886,
117227, 140426, 168120, 126607, 45179, 136348, 168119, 70324, 145270, 34804,
158628, 134865, 185697, 116864, 75083, 132228, 173474, 97172, 138765, 34578,
166299, 178643, 179549, 161244, 50077, 135149, 165339, 185035, 185766,
167884, 193147, 175651, 34701, 160612, 190905, 67087, 138809, 34696, 162242,
95777, 185097, 127573, 163569, 177676, 185008, 34502, 34648, 78124, 145140,
135574, 121075, 183587, 78190, 175903, 194501, 35000, 177581, 43464, 135644,
94275, 166670, 95273, 160051, 116907, 168804, 172381, 95801, 135645, 132267,
166382, 127477, 171389, 117480, 175739, 151786, 165183, 49992, 44335, 60785,
169660, 165827, 184987, 179547, 161912, 169395, 148354, 138647, 34817,
34685, 162789, 165907, 60845, 171421, 191645, 165826, 145220, 117481, 67088,
97174, 164926, 146802, 95799, 34822, 168631, 68773, 34673, 191618, 173691,
67056, 165338, 50039, 127574, 161245, 138085, 158627, 186289, 184985,
141679, 162351, 145073, 152384, 117317, 168118, 127576, 165906, 116910,
138915, 94277, 169810, 148356, 127577, 170906, 117277, 166208, 176062,
178491, 140374, 34442, 173692, 185661, 161347, 165070, 121547, 50023,
185067, 156673, 134942, 177254, 121074, 171418, 70319, 186309, 133921,
127476, 165261, 169086, 178639, 126602, 175979, 185856, 172301, 179588,
117231, 148357, 34759, 174473, 184974, 177355, 140765, 126606, 140149,
170907, 162131, 149019, 117558, 185858, 121552, 190904, 170675, 178935,
67053, 135463, 68770, 138762, 146692, 127479, 160510, 117281, 169194, 78122,
34486, 173690, 127634, 133918, 175652, 78414, 177356, 146743, 75086, 185696,
185065, 140148, 140589, 49977, 185036, 165747, 176728, 34813, 145143,
140117, 175653, 126135, 165748, 117230, 163669, 173608, 177253, 97176,
143696, 146340, 138698, 166765, 116863, 134747, 94278, 185585, 178506,
138811, 149285, 148355, 34997, 67054, 168805, 194502, 187931, 186623,
160508, 126136, 147683, 117228, 136349, 152381, 95776, 185640, 88014,
143538, 89695, 34773, 135230, 135061, 166544, 121548, 39854, 173262, 166543,
138648, 169565, 166384, 140115, 140495, 190653, 165746, 139107, 185722,
160610, 173609, 78416, 126139, 89697, 132262, 136466, 34948, 57578, 34761,
34503, 84921, 158629, 170530, 164618, 183650, 34744, 95773, 165106, 135643,
127575, 185811, 178936, 39956, 34611, 185681, 183588, 191646, 183619,
135642, 117280, 185003, 189380, 39779, 136221, 57874, 127196, 135001, 64480,
146290, 190654, 151894, 41511, 135575, 127636, 121550, 145219, 140324,
152104, 135060, 171388, 60776, 134748, 149283, 177761, 170909, 127475,
149020, 194500, 170603, 39929, 34323, 185641, 172302, 136465, 185698, 89698,
43759, 166381, 140428, 117278, 164616, 134882, 175978, 165824, 182260,
140762, 178702, 120505, 165259, 156564, 39765, 148251, 162459, 160053,
160155, 132231, 127198, 173695, 160052, 156563, 184745, 57878, 67090,
143697, 185068, 186310, 159279, 177758, 168629, 161346, 67052, 78413,
185094, 140494, 148304, 177120, 44278, 187932, 49836, 84922, 158396, 126138,
189381, 95774, 162352, 44816, 185584, 70320, 97175, 146741, 169197, 159276,
184973, 127159, 34988, 34556, 126133, 146287, 67047, 120504, 174472, 126140,
70321, 39804, 194478, 165260, 151897, 132266, 49963, 56721, 127637, 166922,
139108, 146689, 190720, 117557, 67050, 166701, 162020, 146342, 134946,
178510, 134833, 49971, 176729, 172380, 117282, 57877, 179590, 186624,
145141, 147680, 140114, 177255, 146742, 168807, 135000, 163672, 166787,
184187, 185827, 133920, 133971, 160154, 186311, 135460, 167215, 140325,
136351, 34434, 178638, 134999, 193146, 190719, 179548, 95775, 166301, 34683,
116909, 143537, 191733, 116912, 172378, 169084, 167014, 44853, 178486,
176064, 162460, 166921, 135570, 178934, 159176, 138812, 163567, 172379,
183618, 165071, 175904, 59468, 34897, 165181, 40055, 117482, 158399, 166383,
75082, 44319, 183017, 78415, 120503, 78187, 146288, 34966, 178490, 40048,
178507, 84924, 39923, 34317, 149022, 185639, 68772, 187891, 194475, 185857,
34426, 34970, 127158, 177760, 162790, 163568, 163670, 143539, 43741, 39848,
116908, 148634, 132417, 126604, 133972, 120502, 75223, 152383, 126608,
94273, 148735, 185767, 162130, 147681, 88013, 165904, 171419, 139142,
185765, 134949, 117560, 135572, 132264, 168524, 34594, 161348, 34583,
168522, 136467, 191647, 187889, 133973, 117556, 170988, 89734, 134474,
34559, 185093, 164927, 145072, 126603, 67049, 145139, 184986, 140373,
185746, 171420, 39799, 182259, 169397, 168632, 135459, 176065, 45130,
148305, 140763, 134944, 186288, 34694, 169195, 132422, 174489, 34607,
132420, 140371, 185721, 127635, 135228, 44708, 89735, 168523, 138914,
152106, 160050, 60624, 151787, 185005, 133864, 185095, 140592, 185006,
145221, 161911, 49774, 178487, 95276, 95802, 176727, 169661, 34944, 135571,
169196, 175980, 160156, 78411, 34493, 44437, 133919, 166764, 34922, 60846,
34914, 34920, 148632, 162458, 34953, 34363, 173693, 140493, 149286, 175905,
170674, 116860, 57873, 138763, 172300, 166703, 140252, 146804, 57628,
183018, 156674, 50114, 177152, 170602, 162019, 146289, 161809

Hence, we can simplify the previous long query into this:

SELECT "custom_tags"."custom_tag_group_id", custom_tags.name as name,
sum(impressions) as total_impressions, sum(clicks) as total_clicks,
sum(reach) as total_reach, sum(spend_rupees) as total_spend, sum(spend) as
spend_account, sum(actions_link_click) as website_clicks,
sum(actions_mobile_app_install) as mobile_app_install,
sum(actions_app_custom_event) as app_custom_event FROM "custom_tags" INNER
JOIN "custom_tags_fb_ad_groups" ON
"custom_tags_fb_ad_groups"."custom_tag_id" = "custom_tags"."id" INNER JOIN
"fb_ad_groups" ON "fb_ad_groups"."id" =
"custom_tags_fb_ad_groups"."fb_ad_group_id" INNER JOIN "fb_ad_group_reports"
ON "fb_ad_group_reports"."fb_ad_group_id" = "fb_ad_groups"."id" WHERE
"fb_ad_group_reports"."fb_ad_group_id" IN (<array of 1777 fb_ad_group_ids>)
AND (start_timestamp >= '2015-10-31 18:30:00' and start_timestamp <
'2015-11-30 18:30:00') AND "custom_tags"."company_id" = 12 AND
"fb_ad_groups"."ad_account_id" = 29 AND "custom_tags"."custom_tag_group_id"
= 235 GROUP BY "custom_tags"."custom_tag_group_id", custom_tags.name




--
View this message in context:
http://postgresql.nabble.com/Advise-needed-for-a-join-query-with-a-where-conditional-tp5877097p5877098.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.