Re: COUNT(*) and index-only scans

On Mon, Oct 10, 2011 at 3:15 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> Right now, our costing model for index-only scans is pretty dumb.
>> It assumes that using an index-only scan will avoid 10% of the
>> heap fetches.  That could easily be low, and on an insert-only
>> table or one where only the recently-updated rows are routinely
>> accessed, it could also be high.
>
> As a reality check, I just ran this query on a table in a statewide
> copy of our data:
>
> select count(*),
>  sum(case when xmin = '2'::xid then 0 else 1 end) as read_heap
>  from "CaseHist";
>
> and got:
>
>   count   | read_heap
> -----------+-----------
>  205765311 |   3934924
>
> So on our real-world database, it would skip something on the order
> of 98% of the heap reads, right?

Yeah, if it's scanning the whole table.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company