On Thu, Jan 7, 2010 at 10:57 AM, Gurgel, Flavio <flavio@4linux.com.br> wrote:
> ----- "Matthew Wakeling" <matthew@flymine.org> escreveu:
>> On Thu, 7 Jan 2010, Gurgel, Flavio wrote:
>> Postgres does not change a query plan according to the shared_buffers
>>
>> setting. It does not anticipate one step contributing to another step
>> in
>> this way. It does however make use of the effective_cache_size setting
>> to
>> estimate this effect, and that does affect the planner.
>
> That was what I was trying to say :)
>
>> In a situation like this, the opposite will be true. If you were
>> accessing
>> a very small part of a table, say to order by a field with a small
>> limit,
>> then an index can be very useful by providing the results in the
>> correct
>> order. However, in this case, almost the entire table has to be read.
>>
>> Changing the order in which it is read will mean that the disc access
>> is
>> no longer sequential, which will slow things down, not speed them up.
>>
>> The Postgres planner isn't stupid (mostly), there is probably a good
>> reason why it isn't using an index scan.
>
> Sorry but I disagree. This is the typical case where the test has to be made.
> The results are partial, let's say 50% of the table. Considerind that the disk is fast enough, the cost estimation of
sequentialand random reads are in a proportion of 1 to 4, considering default settings in PostgreSQL.
You do know that indexes in postgresql are not "covering" right? I.e.
after hitting the index, the db then has to hit the table to see if
those rows are in fact visible. So there's no such thing in pgsql, at
the moment, as an index only scan.