From: Scott Marlowe <scott.marlowe@gmail.com>
To: Gurgel, Flavio <flavio@4linux.com.br>
Cc: Matthew Wakeling <matthew@flymine.org>
Cc: Lefteris <lsidir@gmail.com>
Cc: pgsql-performance@postgresql.org, Ivan Voras <ivoras@freebsd.org>
Subject: Re: Air-traffic benchmark
Date: Thu, 7 Jan 2010 11:02:13 -0700
Message-ID: <dcc563d11001071002i38214570if8fcc738021116fe@mail.gmail.com> (raw)
In-Reply-To: <27470544.48331262887021660.JavaMail.root@mail.4linux.com.br>
References: <21301378.46621262884592029.JavaMail.root@mail.4linux.com.br>
<27470544.48331262887021660.JavaMail.root@mail.4linux.com.br>
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 sequential and 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, flavio@4linux.com.br, matthew@flymine.org, lsidir@gmail.com, ivoras@freebsd.org
Subject: Re: Air-traffic benchmark
In-Reply-To: <dcc563d11001071002i38214570if8fcc738021116fe@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox