Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 42DDD63263A for ; Sun, 28 Mar 2010 18:04:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 90680-01 for ; Sun, 28 Mar 2010 21:04:41 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f225.google.com (mail-fx0-f225.google.com [209.85.220.225]) by mail.postgresql.org (Postfix) with ESMTP id 469CB632821 for ; Sun, 28 Mar 2010 18:04:46 -0300 (ADT) Received: by fxm25 with SMTP id 25so78695fxm.3 for ; Sun, 28 Mar 2010 14:04:44 -0700 (PDT) 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 :from:date:received:message-id:subject:to:cc:content-type :content-transfer-encoding; bh=diggEu4isqXehuFgQNAiQDrQaDS+PJ6WDjkHAyL7J6U=; b=Lx8zgTRYW1jjoISUUduoC3h2d0iVjJ7TR8H0V91XfX4Bu0y0l8OfHiYwQdUEIzXGkd rUO1/ZBs/pn/zsSnCHAShYZRjz6VRnd/8hR5oVXdYwEAbt2MGTnetuRmqBn92gnP5hfG CudxplmuNkrYDDHoW6KS55dqGrrzP92RstlAA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; b=rFvzViRDFN6KzZrU0D5vP1fG7uzRQ+rOw6i4YULIhgssmb9QRcclTDVOqfuISDlVKm 7b7qjJoBCax/JByihggEWpEY7osBCDXkM0uZ9fVvfRSY8nDsCBi08ZUFuXAdnvgSP9OX 1CHySBSAdBQn5nM2QfRhF+lEXTJHaykw+gs7g= MIME-Version: 1.0 Received: by 10.239.140.197 with HTTP; Sun, 28 Mar 2010 14:04:24 -0700 (PDT) In-Reply-To: <603c8f071003241746i4713bc8fgc55357486d3f0bd3@mail.gmail.com> References: <4BA148CF.5080701@r-stream.com> <1CFD7891521AAB4E8201FB7A78C9D36F05453FD7@mail-001.corp.automotive.com> <603c8f071003241746i4713bc8fgc55357486d3f0bd3@mail.gmail.com> From: =?ISO-8859-1?Q?C=E9dric_Villemain?= Date: Sun, 28 Mar 2010 23:04:24 +0200 Received: by 10.239.134.142 with SMTP id 14mr340653hbz.206.1269810284119; Sun, 28 Mar 2010 14:04:44 -0700 (PDT) Message-ID: Subject: Re: Forcing index scan on query produces 16x faster To: Robert Haas Cc: "Eger, Patrick" , Christian Brink , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.185 tagged_above=-10 required=5 tests=AWL=0.414, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/360 X-Sequence-Number: 38185 2010/3/25 Robert Haas : > On Wed, Mar 17, 2010 at 9:01 PM, Eger, Patrick wro= te: >> I'm running 8.4.2 and have noticed a similar heavy preference for >> sequential scans and hash joins over index scans and nested loops. =A0Ou= r >> database is can basically fit in cache 100% so this may not be >> applicable to your situation, but the following params seemed to help >> us: >> >> seq_page_cost =3D 1.0 >> random_page_cost =3D 1.01 >> cpu_tuple_cost =3D 0.0001 >> cpu_index_tuple_cost =3D 0.00005 >> cpu_operator_cost =3D 0.000025 >> effective_cache_size =3D 1000MB >> shared_buffers =3D 1000MB >> >> >> Might I suggest the Postgres developers reconsider these defaults for >> 9.0 release, or perhaps provide a few sets of tuning params for >> different workloads in the default install/docs? The cpu_*_cost in >> particular seem to be way off afaict. I may be dead wrong though, fwiw >> =3D) > > The default assume that the database is not cached in RAM. =A0If it is, > you want to lower seq_page_cost and random_page_cost to something much > smaller, and typically make them equal. =A0I often recommend 0.005, but > I know others have had success with higher values. > > Ultimately it would be nice to have a better model of how data gets > cached in shared_buffers and the OS buffer cache, but that is not so > easy. I have some work on this point with pgfincore project. Getting the information from the OS is actualy a bit slow but possible. I try to find time to finish my patch in order to get the info in the pg_statio_* views :) > > ...Robert > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org= ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --=20 C=E9dric Villemain