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

Hi Folks,

I am a newbie to this mailing list. Tried searching the forum but didn't
find something similar to the problem I am facing.

Background:
I have a Rails app with Postgres db. For certain reports, I have to join
multiple tables. However, certain join queries are dog slow and I am
wondering if I am missing any index.

Problem:
I have the following tables:
companies

 // belongs to a company
ad_accounts: (id, company_id, other fields)

 // belongs to an ad account and can have many custom tag groups and tags
fb_ad_campaigns (id, ad_account_id, other fields)

 // belongs to a campaign and can have many custom tag groups and tags
fb_ad_sets: (id, ad_account_id, fb_ad_campaign_id, other fields)

 // belongs to an ad set and can have many custom tag groups and tags
fb_ad_groups (id, ad_account_id, fb_ad_campaign_id, fb_ad_set_id, other
fields)

 // belongs to a company and can have many campaigns/adsets/ads
custom_tag_groups: (id, company_id, other fields)

// belongs to a tag group and can have many campaigns/adsets/ads
custom_tags (id, company_id, custom_tag_group_id, other fields)

// association between tags and campaigns
custom_tags_fb_ad_campaigns : (custom_tag_id, fb_ad_campaign_id)

// association between tags and ad sets
custom_tags_fb_ad_sets: (custom_tag_id, fb_ad_set_id)

// association between tags and ad groups
custom_tags_fb_ad_groups: (custom_tag_id, fb_ad_group_id)

// stores performance data of each campaign
fb_ad_campaign_reports : (id, ad_account_id, fb_ad_campaign_id,
start_timestamp,  other fields)

// stores performance data of each adset
fb_ad_set_reports : (id, ad_account_id, fb_ad_campaign_id, fb_ad_set_id,
start_timestamp, other fields)

// stores performance data of each adgroup
fb_ad_group_reports : (id, ad_account_id, fb_ad_campaign_id, fb_ad_set_id,
fb_ad_group_id,  start_timestamp, other fields)

Now a query where we ask give me all custom tags performance data group by a
particular custom tag group where ad sets are one of them in a big array
then the join query is really slow (takes over 132 seconds!):
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 (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)) 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

I used Explain analyse to see where the bottleneck are and here is the
result: http://explain.depesz.com/s/opM7

Am I missing some index that will speed up the computation?

Relevant indices that I have:
fb_ad_groups:
(ad_account_id, fb_ad_set_id)
(ad_account_id, fb_ad_campaign_id)
(fb_ad_set_id)
(fb_ad_campaign_id)

fb_ad_group_reports:
(ad_account_id, fb_ad_campaign_id, start_timestamp)
(ad_account_id, fb_ad_group_id, start_timestamp)
(ad_account_id, fb_ad_set_id, start_timestamp)
(ad_account_id, start_timestamp)
(fb_ad_set_id, start_timestamp)
(fb_ad_group_id, start_timestamp)
(fb_ad_campaign_id, start_timestamp)

custom_tags:
(custom_tag_group_id)
(company_id, custom_tag_group_id)

custom_tags_fb_ad_groups:
(custom_tag_id, fb_ad_group_id)


Thanks and apologies for the long post!




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

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.

On 12/10/15 10:38 PM, ankur_adwyze wrote:
> I have a Rails app with Postgres db. For certain reports, I have to join
> multiple tables. However, certain join queries are dog slow and I am
> wondering if I am missing any index.

My guess is that the planner is coming up with a bad estimate. Please
post the output of EXPLAIN ANALYZE, preferably via
http://explain.depesz.com/
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On Thu, Dec 10, 2015 at 8:38 PM, ankur_adwyze <ankur@adwyze.com> wrote:
> Hi Folks,
>
> I am a newbie to this mailing list. Tried searching the forum but didn't
> find something similar to the problem I am facing.
>
> Background:
> I have a Rails app with Postgres db. For certain reports, I have to join
> multiple tables. However, certain join queries are dog slow and I am
> wondering if I am missing any index.

Are you vacuuming and analyzing your database appropriately?  What
non-default config settings do you have.

Something certainly seems suspicious about custom_tags_fb_ad_groups
and its index.


->  Index Only Scan using custom_tags_fb_ad_groups_index on
custom_tags_fb_ad_groups custom_tags_fb_ad_groups_1
(cost=0.42..1728.30 rows=1 width=8) (actual time=1.352..3.815 rows=1
loops=32934)
     Index Cond: (fb_ad_group_id = fb_ad_group_reports.fb_ad_group_id)
    Heap Fetches: 32934

Doing a single-value look up into an index should have an estimated
cost of around 9, unless you did something screwy with your cost_*
parameter settings.  Why does it think it is 1728.30 instead?  Is the
index insanely bloated?  And it actually is slow to do those look ups,
which is where almost all of your time is going.

And, why isn't it just using a hash join on that table, since you are
reading so much of it?

I'd do a VACUUM FULL of that table, then a regular VACUUM on it (or
the entire database), then ANALYZE it (or your entire database), and
see if that took care of the problem.