Re: Count(*) slower than *

From: joel garry <joel-garry_at_home.com>
Date: Mon, 15 Mar 2010 15:59:27 -0700 (PDT)
Message-ID: <7d16950e-3c67-4816-9ef4-29de27fb5614_at_a16g2000pre.googlegroups.com>

On Mar 15, 2:22 pm, ddf <orat..._at_msn.com> wrote:
> On Mar 15, 4:54 pm, "bob123" <bob..._at_gmail.com> wrote:

>
> >         INDEX (FULL SCAN) OF 'S_PARTY_P1' (UNIQUE) (Cost=95 Card=263570
> > Bytes=2899270)
>

Versus:

>
> >             INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
>

>
> >       INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
>

>
> Select * can return data immediately even if there are more records to
> fetch; count(*) must process all records meeting the criteria before
> returning the desired value.  Examine both execution plans and you see
> a SORT(AGGREGATE) for the count(*) query which is missing in the plain
> vanilla select; also note that the plain select generated 4368
> consistent gets and 0 physical reads where the count(*) query
> generated 163991324 consistent gets and 452 physical reads.  Running
> the count(*) query first may have 'primed' the database for the second
> query which uses exactly the same critera from exactly the same
> tables.  Have you run the count(*) qiuery a second time, after the
> select * offering, and if so is the time tor return the result
> dimished?
>
> David Fitzjarrell

The preloading may have been part of it, but it was only 452 physical reads. I think the giant number of logical reads shows that the optimizer used a different criterion to calculate the relative values of the indices. I'm wondering if this is because count(*) tries harder to get the data from indices, and this is a case where it decides wrong. Also, that full scan of the index may be the only reason for the physical reads, as your suggestion of trying for repeatability may show.

Better to read the proper things twice than a whole lot more once.

I'm also curious if select count(t3_sparty) would do something different.

jg

--
_at_home.com is bogus.
Is anyone ever going to test with induction from 114Kv power lines
nearby? http://www.signonsandiego.com/news/2010/mar/15/toyota-dismisses-account-of-runaway-prius/

Received on Mon Mar 15 2010 - 17:59:27 CDT