Optimize update query

From: Niels Kristian Schjødt <nielskristian(at)autouncle(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Optimize update query
Date: 2012-11-28 12:57:49
Message-ID: 1C199C0D-13DF-47B3-944A-B0560C6D2E5A@autouncle.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Willem Leenen 2012-11-28 13:10:21 Re: Database design - best practice
Previous Message Niels Kristian Schjødt 2012-11-28 12:41:14 Database design - best practice