Re: Optimizing select count query which often takes over 10 seconds

Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>> Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:
>>
>>> update pref_users set medals = 0;
>>> UPDATE 223456
>>
>> You're probably going to like your performance a lot better if
>> you modify that to:
>>
>> update pref_users set medals = 0 where medals <> 0;
>
> is it really so?

Yes.

> I only have 65 users (out of 223456) with medals != 0.

That's precisely the point.  You don't want to update all 223456
rows when there are only 65 which need to be changed.

> When programming other languages, I never do
> if (x != 0) { x = 0; } but just set x = 0 straight away.

Well, if updating a row was as cheap as assigning zero to x I
wouldn't suggest a change to your code.  If assigning something to
x involved an expensive function or disk access, you might try to
put an "if" around it.

If you don't want to burden your query with the condition, you
could consider attaching a trigger to every table that you might
want to assign existing values to rows.  See the
suppress_redundant_updates_trigger() function for details:

http://www.postgresql.org/docs/current/interactive/functions-trigger.html

-Kevin