Re: 9.2 and index only scans

On Sun, Aug 26, 2012 at 12:58 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> Jeff Janes wrote on 26.08.2012 20:45:
>
>> 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.
>
>
> I tried it also with 750000 rows filled with 3 text columns of random string
> (between 20 and 15000 characters long).

Could you show that in a copy-and-paste-able example?

> But also with that bigger data I just don't get an index scan.

Did you change random_page_cost?

> Seems that the prerequisites for an index only scan to happen are quite
> narrow.

Unrestricted count(*) is itself a pretty narrow use case. It is not
the one for which IOS is most likely to prove useful.

> But given the fact that it's a brand new feature I guess it will improve
> over time ;)

When I force an index-only scan (set enable_seqscan=off) it turned out
to be very slightly slower than the sequential scan, so the planner
was getting it right, but perhaps not for the right reason.

Cheers,

Jeff