Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B02E9632284 for ; Thu, 7 Jan 2010 14:02:26 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 96648-03 for ; Thu, 7 Jan 2010 18:02:14 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.27]) by mail.postgresql.org (Postfix) with ESMTP id B169663356F for ; Thu, 7 Jan 2010 14:02:15 -0400 (AST) Received: by ey-out-2122.google.com with SMTP id 22so2905362eye.33 for ; Thu, 07 Jan 2010 10:02:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=dJWmZEuyESF3SMyzJLsIQV0UZpnMVSv6L9BLBGQqS1c=; b=IczNeubBHvVDaO7Q7QfeWHRrKtDfEgeMqPEO6/CGf7zMvTwIl8XcK2Ldf16hLH2U0S kaDVk6PZm8fvU0z1g2O0zu0VYAhJHihjjiFEznlqAxKYhUFwWHO6WMHqag7lURVkyb6F AeI539lwMSHr0rFIOrNEtZtPbpEkrLwHwNZ14= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=XZiTfFOlByd1RkMfDYGHtVA1kWFnN5mpG3osLMJWvfoXYdHzFJjmGAvoqMsoEBNrzK hhFhjT8Kl9zyjRvaZLBzq5neAvKuJ5/FFezACkD9AwWRlVa9fFpsh/NwIe6BiyOS5dk1 Ood2jy6EIkkDItQJTnfxpMolMVyTt4TaoW0Kk= MIME-Version: 1.0 Received: by 10.216.91.66 with SMTP id g44mr549857wef.121.1262887333977; Thu, 07 Jan 2010 10:02:13 -0800 (PST) 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> Date: Thu, 7 Jan 2010 11:02:13 -0700 Message-ID: Subject: Re: Air-traffic benchmark From: Scott Marlowe To: "Gurgel, Flavio" Cc: Matthew Wakeling , Lefteris , pgsql-performance@postgresql.org, Ivan Voras Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.331 tagged_above=-10 required=5 tests=AWL=0.268, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201001/112 X-Sequence-Number: 36992 On Thu, Jan 7, 2010 at 10:57 AM, Gurgel, Flavio wrote: > ----- "Matthew Wakeling" 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.