Re: 9.2 and index only scans

On Sun, Aug 26, 2012 at 8:02 AM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> Tom Lane wrote on 26.08.2012 16:31:
>
>> Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
>>>
>>> I'm playing around with 9.2 beta4 and was looking into the new Index Only
>>> Scan feature.
>>> I was a bit surprised that a "count(*)" query does not use an index.
>>
>>
>> Works for me. However, the cost estimate for that is heavily dependent
>> on how much of the table is known all-visible. If the table is getting
>> a lot of churn, or even just hasn't been vacuumed since it quiesced,
>> the planner will prefer a seqscan for this --- and it will be right.
>>
>
> Hmm. So it's something with my environment.
>
> Should the following setup qualify for an index scan?

The seq scan is estimated to use sequential reads, while the
index-only scan is estimated to use random reads (because the index is
scanned in logical order, not physical order).

If you set random_page_cost equal to seq_page_cost, that would
artificially favor the index only scan.

Also, your filler is highly compressible, which means the table is
much smaller than you might think.

Cheers,

Jeff