Hi, i have these update queries, that run very often, and takes too long time, in order for us to reach the throughput
weare aiming at. However, the update query is very simple, and I can't figure out any way to improve the situation. The
querylooks like this:
UPDATE "adverts" SET "last_observed_at" = '2012-11-28 00:02:30.265154', "data_source_id"
='83d024a57bc2958940f3ca281bddcbf4'WHERE"adverts"."id" IN ( 1602382, 4916432, 3221246, 4741057, 3853335, 571429,
3222740,571736, 3544903, 325378,5774338, 5921451, 4295768, 3223170, 5687001, 4741966, 325519, 580867, 325721, 4412200,
4139598,325567, 1616653,1616664, 6202007, 3223748, 325613, 3223764, 325615, 4296536, 3854595, 4971428, 3224146,
5150522,4412617, 5073048,325747, 325771, 1622154, 5794384, 5736581, 1623767, 5686945, 3224627, 5073009, 3224747,
3224749,325809, 5687051,3224811, 5687052, 4917824, 5073013, 3224816, 3224834, 4297331, 1623907, 325864, 1623947,
6169706,325869, 325877,3225074, 3225112, 325893, 325912, 3225151, 3225184, 3225175, 1624659, 325901, 4033926, 325904,
325911,4412835,1624737, 5073004, 5921434, 325915, 3225285, 3225452, 4917672, 1624984, 3225472, 325940, 5380611, 325957,
5073258,3225500,1625002, 5923489, 4413009, 325952, 3961122, 3637777 ) ;
An explain outputs me the following:
"Update on adverts (cost=0.12..734.27 rows=95 width=168)"
" -> Index Scan using adverts_pkey on adverts (cost=0.12..734.27 rows=95 width=168)"
" Index Cond: (id = ANY
('{1602382,4916432,3221246,4741057,3853335,571429,3222740,571736,3544903,325378,5774338,5921451,4295768,3223170,5687001,4741966,325519,580867,325721,4412200,4139598,325567,1616653,1616664,6202007,3223748,325613,3223764,325615,4296536,3854595,4971428,3224146,5150522,4412617,5073048,325747,325771,1622154,5794384,5736581,1623767,5686945,3224627,5073009,3224747,3224749,325809,5687051,3224811,5687052,4917824,5073013,3224816,3224834,4297331,1623907,325864,1623947,6169706,325869,325877,3225074,3225112,325893,325912,3225151,3225184,3225175,1624659,325901,4033926,325904,325911,4412835,1624737,5073004,5921434,325915,3225285,3225452,4917672,1624984,3225472,325940,5380611,325957,5073258,3225500,1625002,5923489,4413009,325952,3961122,3637777}'::integer[]))"
So as you can see, it's already pretty optimized, it's just not enough :-) So what can I do? the two columns
last_observed_atand data_source_id has an index, and it is needed elsewhere, so I can't delete those.
PS. I'm on postgres 9.2 on a server with 32gb ram, 8 cores and two 3T disks in a software raid 1 setup.
Is the only way out of this really a SSD disk?