Postgres query optimization

join-values-explain.txt

 Nested Loop  (cost=0.42..4594.88 rows=830 width=430) (actual time=0.038..18.896 rows=829 loops=1)
   ->  Values Scan on "*VALUES*"  (cost=0.00..10.40 rows=832 width=4) (actual time=0.005..2.557 rows=832 loops=1)
   ->  Index Scan using trait_values_pkey on trait_values  (cost=0.42..5.50 rows=1 width=430) (actual time=0.006..0.009 rows=1 loops=832)
         Index Cond: (id = "*VALUES*".column1)
         Filter: (((state)::text = 'accepted'::text) AND (public_state = 'published'::trait_value_public_state))
         Rows Removed by Filter: 0
 Total runtime: 21.202 ms

join-values.sql

explain analyze
SELECT "trait_values".* 
FROM "trait_values" 
JOIN (VALUES (270185), (2597), (1151), (80790), (82999), (5482), (7), (4790), (80791), (140382), (1153), (92), (5694), (4697), (64), (223458), (410997), (410998), (931), (475256), (475263), (475264), (62), (475255), (475257), (51), (475265), (140381), (1156), (93), (475266), (5409), (1157), (475267), (33795), (458254), (458255), (183), (458261), (6323), (458260), (458262), (1115), (1148), (458259), (458257), (475258), (4120), (172), (4119), (458263), (458256), (1131), (475268), (5201), (458265), (458264), (104), (458281), (55), (4010), (458273), (458306), (12895), (83000), (458269), (22), (171), (1161), (458266), (95), (475269), (5501), (344437), (458287), (458275), (458315), (458276), (458288), (130889), (458278), (458258), (458274), (30141), (458325), (458301), (458272), (458282), (458289), (410449), (458303), (1033), (458290), (410453), (458304), (458291), (15989), (976), (972), (15988), (410445), (344438), (410446), (458295), (458284), (458577), (4003), (410462), (458280), (458270), (127117), (213), (410447), (410454), (458293), (6849), (458283), (458318), (410456), (410470), (410448), (83002), (23), (458268), (458292), (458583), (410459), (458609), (458607), (1158), (410461), (24268), (410450), (458267), (458982), (546), (458608), (137126), (40970), (410455), (83001), (15987), (458984), (410464), (12300), (458595), (15990), (458307), (122535), (323077), (458300), (193730), (342930), (458298), (5443), (410463), (458271), (401730), (8514), (536), (401729), (122513), (410474), (458314), (410452), (15991), (15986), (410489), (6297), (166914), (396715), (401728), (401732), (458294), (1152), (4152), (410466), (441629), (122442), (458326), (924), (458277), (121967), (42244), (410460), (967), (167846), (971), (410467), (1086), (410451), (831), (410478), (458983), (458578), (410457), (458279), (458297), (458286), (410471), (145022), (21176), (410458), (410469), (1080), (323073), (458989), (410468), (77348), (401733), (458985), (387729), (458308), (410479), (206795), (323063), (105982), (458324), (323066), (458305), (4092), (6499), (122570), (30921), (458299), (458302), (458620), (121986), (458582), (458992), (21245), (121910), (410473), (110868), (901), (21509), (458311), (121938), (18044), (400916), (401735), (458991), (323061), (323070), (192), (669), (458604), (458588), (121992), (176659), (5444), (323074), (394671), (458988), (410465), (323076), (195), (323056), (387728), (314475), (118202), (4727), (288359), (458606), (460025), (286), (460060), (401740), (323071), (293161), (568), (458579), (410996), (121916), (410491), (410477), (458317), (212), (323065), (268430), (410481), (868), (167772), (917), (410483), (19272), (5960), (401734), (197836), (121954), (83333), (458625), (458987), (410485), (458601), (364265), (7385), (2574), (459023), (293002), (213920), (459011), (1025), (458619), (9659), (1037), (402166), (122417), (458594), (458328), (111752), (15992), (32712), (424624), (460104), (458590), (60), (276289), (105), (6129), (323064), (696), (29036), (460126), (458576), (401741), (383858), (401736), (6144), (410486), (121935), (460116), (410484), (410472), (154148), (2611), (459000), (223463), (122450), (458574), (458994), (76001), (458323), (276580), (4156), (410497), (26726), (122477), (8249), (1053), (23538), (459004), (459009), (410476), (345658), (458312), (78), (19555), (459014), (9310), (33875), (338201), (8145), (459029), (460072), (458322), (401737), (121920), (16311), (2608), (694), (750), (950), (410514), (146599), (340982), (410541), (410488), (335753), (308494), (459003), (410522), (458596), (5741), (458569), (341021), (323079), (401747), (4614), (718), (460125), (410502), (402198), (257172), (410506), (458627), (323068), (410480), (410509), (7809), (122458), (121914), (402170), (2560), (401743), (410498), (402173), (323067), (410547), (459001), (16313), (121980), (410556), (416), (121928), (122429), (77481), (410530), (410482), (121918), (220), (1059), (1132), (458586), (458573), (460046), (462042), (1032), (458571), (402234), (460201), (949), (410571), (1128), (459013), (460106), (384907), (124850), (339166), (458558), (672), (458993), (9764), (458986), (121941), (16863), (458561), (625), (784), (99928), (458285), (458568), (459018), (179), (410521), (458589), (410572), (460205), (410500), (1070), (121881), (460117), (780), (542), (460203), (663), (460213), (402230), (410586), (689), (460115), (418340), (459021), (458603), (460030), (458570), (410490), (691), (458584), (460223), (458605), (532), (935), (314477), (458560), (460181), (460220), (460064), (295408), (753), (461185), (1113), (458995), (32753), (460050), (402179), (459820), (364260), (462009), (460067), (257166), (773), (33449), (410554), (8902), (1084), (268433), (458587), (410516), (462019), (1041), (337661), (1039), (257154), (257148), (377), (401731), (131533), (458320), (22719), (76715), (458990), (460034), (460071), (94), (14244), (14245), (119369), (410526), (402194), (121962), (120243), (25509), (706), (1063), (402600), (460122), (402614), (176884), (122486), (458559), (19935), (410487), (338053), (719), (422), (2621), (39582), (402595), (451309), (43208), (257145), (1042), (402171), (459019), (223490), (111568), (460132), (458598), (721), (459007), (121926), (121946), (121999), (410524), (953), (460123), (121961), (95586), (424709), (76398), (467781), (417609), (410566), (1079), (460040), (460038), (460216), (353900), (122738), (271), (460036), (127930), (5742), (7313), (1044), (187162), (402167), (460026), (293240), (1139), (619), (155732), (402172), (458602), (460053), (121973), (460078), (402169), (121905), (176573), (315458), (826), (410492), (410511), (122003), (85737), (121897), (401744), (410533), (462047), (583), (344439), (36340), (401739), (257133), (458296), (220268), (460031), (1061), (460087), (459030), (181798), (1150), (396704), (10807), (458563), (458309), (682), (223469), (130591), (320391), (271898), (766), (458567), (151044), (257149), (110744), (330149), (410494), (387727), (459027), (458621), (460032), (402596), (512), (837), (298841), (734), (410592), (460214), (121923), (460179), (135045), (463626), (402620), (323075), (458316), (176790), (9801), (257600), (126024), (925), (410503), (410565), (460057), (458996), (294433), (615), (4496), (297303), (629), (6759), (9454), (460035), (443274), (4607), (417604), (459031), (321239), (92618), (16996), (515), (458566), (459022), (459020), (458610), (257139), (257597), (402937), (458321), (460076), (323083), (987), (294230), (460044), (458624), (460041), (429), (99074), (99072), (460174), (186247), (673), (454133), (458565), (458623), (122424), (711), (219), (337732), (501), (295048), (99065), (163474), (173091), (410548), (462028), (460130), (460184), (460083), (121948), (1062), (295834), (294389), (573), (443287), (410542), (270), (458613), (402615), (323072), (461986), (313822), (410517), (459025), (421), (489), (460215), (461531), (461759), (402625), (299662), (188072), (114746), (473), (458998), (459012), (121925), (402197), (42973), (402199), (410546), (410561), (458572), (462032), (902), (458600), (14726), (364266), (1028), (624), (459911), (577), (364273), (513), (459008), (478), (419122), (401754), (268091), (410577), (410579), (460173), (460081), (460062), (460068), (1045), (401745), (410535), (462025), (427), (508), (121970), (402233), (458622), (461485), (122519), (486), (333468), (461520), (121888), (293455), (400909), (410559), (461486), (462048), (257206), (455), (121971), (360225), (401752), (410580), (461980), (410543), (176670), (461984), (1090), (50), (458592), (1060), (401753), (410508), (424625), (549), (521), (29213), (122443), (121908), (402227), (18226), (1073), (410575), (460028), (135484), (467707), (16312), (86384), (980), (728), (5207), (401738))
  AS v(id) USING (id)
WHERE "trait_values"."state" = 'accepted' AND "trait_values"."public_state" = 'published';

where-in-explain.txt

Bitmap Heap Scan on trait_values  (cost=1714.84..4291.00 rows=830 width=430) (actual time=1.397..5.245 rows=829 loops=1)
   Recheck Cond: (id = ANY ('{270185,2597,1151,80790,82999,
   
Filter: (((state)::text = 'accepted'::text) AND (public_state = 'published'::trait_value_public_state))
   Rows Removed by Filter: 3
   ->  Bitmap Index Scan on trait_values_pkey  (cost=0.00..1714.64 rows=832 width=0) (actual time=1.318..1.318 rows=832 loops=1)
         Index Cond: (id = ANY ('{270185,2597,1151,80790,82999,5482,7,
         
 Total runtime: 8.393 ms

where-in.sql

explain analyze 
SELECT "trait_values".* 
FROM "trait_values" 
WHERE "trait_values"."state" = 'accepted' 
  AND "trait_values"."public_state" = 'published' 
  AND "trait_values"."id" IN (270185, 2597, 1151, 80790, 82999, 5482, 7, 4790, 80791, 140382, 1153, 92, 5694, 4697, 64, 223458, 410997, 410998, 931, 475256, 475263, 475264, 62, 475255, 475257, 51, 475265, 140381, 1156, 93, 475266, 5409, 1157, 475267, 33795, 458254, 458255, 183, 458261, 6323, 458260, 458262, 1115, 1148, 458259, 458257, 475258, 4120, 172, 4119, 458263, 458256, 1131, 475268, 5201, 458265, 458264, 104, 458281, 55, 4010, 458273, 458306, 12895, 83000, 458269, 22, 171, 1161, 458266, 95, 475269, 5501, 344437, 458287, 458275, 458315, 458276, 458288, 130889, 458278, 458258, 458274, 30141, 458325, 458301, 458272, 458282, 458289, 410449, 458303, 1033, 458290, 410453, 458304, 458291, 15989, 976, 972, 15988, 410445, 344438, 410446, 458295, 458284, 458577, 4003, 410462, 458280, 458270, 127117, 213, 410447, 410454, 458293, 6849, 458283, 458318, 410456, 410470, 410448, 83002, 23, 458268, 458292, 458583, 410459, 458609, 458607, 1158, 410461, 24268, 410450, 458267, 458982, 546, 458608, 137126, 40970, 410455, 83001, 15987, 458984, 410464, 12300, 458595, 15990, 458307, 122535, 323077, 458300, 193730, 342930, 458298, 5443, 410463, 458271, 401730, 8514, 536, 401729, 122513, 410474, 458314, 410452, 15991, 15986, 410489, 6297, 166914, 396715, 401728, 401732, 458294, 1152, 4152, 410466, 441629, 122442, 458326, 924, 458277, 121967, 42244, 410460, 967, 167846, 971, 410467, 1086, 410451, 831, 410478, 458983, 458578, 410457, 458279, 458297, 458286, 410471, 145022, 21176, 410458, 410469, 1080, 323073, 458989, 410468, 77348, 401733, 458985, 387729, 458308, 410479, 206795, 323063, 105982, 458324, 323066, 458305, 4092, 6499, 122570, 30921, 458299, 458302, 458620, 121986, 458582, 458992, 21245, 121910, 410473, 110868, 901, 21509, 458311, 121938, 18044, 400916, 401735, 458991, 323061, 323070, 192, 669, 458604, 458588, 121992, 176659, 5444, 323074, 394671, 458988, 410465, 323076, 195, 323056, 387728, 314475, 118202, 4727, 288359, 458606, 460025, 286, 460060, 401740, 323071, 293161, 568, 458579, 410996, 121916, 410491, 410477, 458317, 212, 323065, 268430, 410481, 868, 167772, 917, 410483, 19272, 5960, 401734, 197836, 121954, 83333, 458625, 458987, 410485, 458601, 364265, 7385, 2574, 459023, 293002, 213920, 459011, 1025, 458619, 9659, 1037, 402166, 122417, 458594, 458328, 111752, 15992, 32712, 424624, 460104, 458590, 60, 276289, 105, 6129, 323064, 696, 29036, 460126, 458576, 401741, 383858, 401736, 6144, 410486, 121935, 460116, 410484, 410472, 154148, 2611, 459000, 223463, 122450, 458574, 458994, 76001, 458323, 276580, 4156, 410497, 26726, 122477, 8249, 1053, 23538, 459004, 459009, 410476, 345658, 458312, 78, 19555, 459014, 9310, 33875, 338201, 8145, 459029, 460072, 458322, 401737, 121920, 16311, 2608, 694, 750, 950, 410514, 146599, 340982, 410541, 410488, 335753, 308494, 459003, 410522, 458596, 5741, 458569, 341021, 323079, 401747, 4614, 718, 460125, 410502, 402198, 257172, 410506, 458627, 323068, 410480, 410509, 7809, 122458, 121914, 402170, 2560, 401743, 410498, 402173, 323067, 410547, 459001, 16313, 121980, 410556, 416, 121928, 122429, 77481, 410530, 410482, 121918, 220, 1059, 1132, 458586, 458573, 460046, 462042, 1032, 458571, 402234, 460201, 949, 410571, 1128, 459013, 460106, 384907, 124850, 339166, 458558, 672, 458993, 9764, 458986, 121941, 16863, 458561, 625, 784, 99928, 458285, 458568, 459018, 179, 410521, 458589, 410572, 460205, 410500, 1070, 121881, 460117, 780, 542, 460203, 663, 460213, 402230, 410586, 689, 460115, 418340, 459021, 458603, 460030, 458570, 410490, 691, 458584, 460223, 458605, 532, 935, 314477, 458560, 460181, 460220, 460064, 295408, 753, 461185, 1113, 458995, 32753, 460050, 402179, 459820, 364260, 462009, 460067, 257166, 773, 33449, 410554, 8902, 1084, 268433, 458587, 410516, 462019, 1041, 337661, 1039, 257154, 257148, 377, 401731, 131533, 458320, 22719, 76715, 458990, 460034, 460071, 94, 14244, 14245, 119369, 410526, 402194, 121962, 120243, 25509, 706, 1063, 402600, 460122, 402614, 176884, 122486, 458559, 19935, 410487, 338053, 719, 422, 2621, 39582, 402595, 451309, 43208, 257145, 1042, 402171, 459019, 223490, 111568, 460132, 458598, 721, 459007, 121926, 121946, 121999, 410524, 953, 460123, 121961, 95586, 424709, 76398, 467781, 417609, 410566, 1079, 460040, 460038, 460216, 353900, 122738, 271, 460036, 127930, 5742, 7313, 1044, 187162, 402167, 460026, 293240, 1139, 619, 155732, 402172, 458602, 460053, 121973, 460078, 402169, 121905, 176573, 315458, 826, 410492, 410511, 122003, 85737, 121897, 401744, 410533, 462047, 583, 344439, 36340, 401739, 257133, 458296, 220268, 460031, 1061, 460087, 459030, 181798, 1150, 396704, 10807, 458563, 458309, 682, 223469, 130591, 320391, 271898, 766, 458567, 151044, 257149, 110744, 330149, 410494, 387727, 459027, 458621, 460032, 402596, 512, 837, 298841, 734, 410592, 460214, 121923, 460179, 135045, 463626, 402620, 323075, 458316, 176790, 9801, 257600, 126024, 925, 410503, 410565, 460057, 458996, 294433, 615, 4496, 297303, 629, 6759, 9454, 460035, 443274, 4607, 417604, 459031, 321239, 92618, 16996, 515, 458566, 459022, 459020, 458610, 257139, 257597, 402937, 458321, 460076, 323083, 987, 294230, 460044, 458624, 460041, 429, 99074, 99072, 460174, 186247, 673, 454133, 458565, 458623, 122424, 711, 219, 337732, 501, 295048, 99065, 163474, 173091, 410548, 462028, 460130, 460184, 460083, 121948, 1062, 295834, 294389, 573, 443287, 410542, 270, 458613, 402615, 323072, 461986, 313822, 410517, 459025, 421, 489, 460215, 461531, 461759, 402625, 299662, 188072, 114746, 473, 458998, 459012, 121925, 402197, 42973, 402199, 410546, 410561, 458572, 462032, 902, 458600, 14726, 364266, 1028, 624, 459911, 577, 364273, 513, 459008, 478, 419122, 401754, 268091, 410577, 410579, 460173, 460081, 460062, 460068, 1045, 401745, 410535, 462025, 427, 508, 121970, 402233, 458622, 461485, 122519, 486, 333468, 461520, 121888, 293455, 400909, 410559, 461486, 462048, 257206, 455, 121971, 360225, 401752, 410580, 461980, 410543, 176670, 461984, 1090, 50, 458592, 1060, 401753, 410508, 424625, 549, 521, 29213, 122443, 121908, 402227, 18226, 1073, 410575, 460028, 135484, 467707, 16312, 86384, 980, 728, 5207, 401738);