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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
