Re: Slow update

On Feb 9, 2009, at 2:07 PM, Grzegorz Jaśkiewicz wrote:

> On Mon, Feb 9, 2009 at 12:50 PM, Herouth Maoz
> <herouth(at)unicell(dot)co(dot)il> wrote:
>> I hope someone can clue me in based on the results of explain
>> analyze.
>
> Did you have a chance to run vmstat on it, and post it here ? Maybe -
> if db resides on the same disc with everything else, something
> (ab)uses that much io, and it has to wait.
> Also, I don't know - but personaly I didn't like the line in explain:
>
> -> Bitmap Index Scan on billing_msisdn_sme_reference
> (cost=0.00..24.70 rows=389 width=0) (actual time=2
> 1.418..21.418 rows=252 loops=151332)
> Index Cond: ((b.msisdn)::text =
> (("substring"((rb.msisdn)::text, 1, 0) || '972'::text) || "substrin
> g"((rb.msisdn)::text, 2)))
>
> But the cost is next to none, so that's not it.

Actually, it's inside a nested loop and if I read correctly it gets
looped over 151332 times. That means it takes 151332 * (21.418 -
1.418) = 3026640 ms, which is almost 12% of the total time.

The biggie seems to be the bitmap heap scan on rb though. The row
estimates for that one are way off (estimated 549 rows vs actual
151332).

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4991338b747034711712127!