Hi, i have these update queries, that run very often, and takes too long time, in order for us to reach the throughput we are aiming at. However, the update query is very simple, and I can't figure out any way to improve the situation. The query looks 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_at and 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?
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
