Re: Slow performance on count(*)?

On 7¿ù15ÀÏ, ¿ÀÈÄ10½Ã53ºÐ, groups.brob..._at_gmail.com wrote:
> On Jul 14, 8:53 pm, Dion Cho <ukja.d..._at_gmail.com> wrote:
>
> > It's interesting that "index full scan" is being used here, but it's
> > quite possible.
>
> > This is not a degenerate case, actually very common and natural case.
> > The simple fact is that delete never deallocates the free space.
> > Only truncate or rebuild does it.
>
> > Dion Cho
>
> Well, I call it a degenerate case as it would be something that would
> only occur in:
>
> 1. An unconstrained query (or one that didn't constrain against
> indexed values)
> 2. against a table with a large number number of rows
> 3. that had had a large percentage of those rows deleted
>
> From what has been said so far, all three of these conditions need to
> be true in order for you to see poor performance like this. It is is
> unusual for any app we write to do unconstrained queries against very
> large tables; it is also unusual for us to dramatically reduce the
> size of a particular table once it is in production via a massive
> delete. The only common real-world case where I can see something
> like this happening is in batch-processing situation where
> intermediate staging tables are loaded up with production data and
> subsequently queried, then cleaned out; however, in this case, I would
> expect that you would use global temporary tables for the
> intermediaries.
>
> Can you think of other examples where this type of problem could
> arise? Our application (which has been running 24/7 since 1998 and
> has been upgraded many times; we've always handled our data reaping by
> running nightly jobs to remove the oldest day's worth of data, and we
> haven't run into issues with this strategy. We'd like to eventually
> move to using partitioned tables to make the reaping a little more
> elegant, but the nature of the data is such it is rather tricky to do
> so without a significant amount of work & migration (as new data has
> references to older data, so it's not a simple matter of partitioning
> by create date). We've had no real issues, presumably, because (1)
> our queries all go against indexes and (2) the daily volume on the
> system grows at a very slow rate (maybe 100% per year), so there isn't
> really an issue with "shrinkage", even though the tables probably have
> a fair amount of wasted space (due to holes not filling up until pages
> completely free up).

Think about your execution plan.
It's index full scan, not index fast full scan or table full scan. If you had added some beautiful predicates(like where xxx > 0), it would have followed index range scan.

And index has almost zero keys and leaf blocks!!! Even in that case, you still have performance problem.

It's because, the leaf node chain is not broken by deletion. Only table truncation, index rebuilding or coalescing do that.

Dion Cho Received on Tue Jul 15 2008 - 19:53:13 CDT