Optimize update query
От | Niels Kristian Schjødt |
---|---|
Тема | Optimize update query |
Дата | |
Msg-id | 1C199C0D-13DF-47B3-944A-B0560C6D2E5A@autouncle.com обсуждение исходный текст |
Ответы |
Re: Optimize update query
|
Список | pgsql-performance |
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?
В списке pgsql-performance по дате отправления: