Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 93400633284 for ; Thu, 26 Mar 2009 00:19:22 -0300 (ADT) 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 80285-09 for ; Thu, 26 Mar 2009 00:19:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.31]) by mail.postgresql.org (Postfix) with ESMTP id 248C36327BF for ; Thu, 26 Mar 2009 00:19:14 -0300 (ADT) Received: by yw-out-2324.google.com with SMTP id 3so250044ywj.73 for ; Wed, 25 Mar 2009 20:19:13 -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 :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=piQq4RbNa3woesbhvjKl5Np3uA2HmKdYA52BHmkQVxU=; b=frHz4gX8dJaWEf/h89HUkJEDf1Mpcu2MHMbDMFMeOeOdFjCHUBjVUdXvpCQlwk3eks e07VWuIc1KCPWJ8q2EtZu2bk9rFP/RnqadnPyNqCqq2q50yW1N+bzNMAfFdxW2R3Z/2u xUkN1iXNTsDP7df+V7UlY3s/hUYu5/h0eNhFM= 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:content-transfer-encoding; b=tNGYu/5ae1fPHOKR8W76vV3JSXc04yhfLZsN8yAE0pNP79a2nfQcpKIHCDfnekiMW5 sQakKtZtTUmonXQ1eKXy1i4Akgq3Hrj9Q9dAT4Ww7UmMTfT7atrY97ll5mnmhc27Mrw5 VdfLSFBhV6hSb3FZOZ1qOU2MeaskoMhPG6/2c= MIME-Version: 1.0 Received: by 10.100.138.17 with SMTP id l17mr196189and.76.1238037553838; Wed, 25 Mar 2009 20:19:13 -0700 (PDT) In-Reply-To: <11650.1238033714@sss.pgh.pa.us> References: <7fd310d10903231203r1bd37b8cn8baeadae83253f44@mail.gmail.com> <7fd310d10903241521v1cbf5d7aueca653e03ed98649@mail.gmail.com> <7fd310d10903242047t1a7b7daw1d61c3de7f20b7c6@mail.gmail.com> <49CAA85C.8080402@agliodbs.com> <7fd310d10903251514x1dd522f3o8d13a9218a6eda6c@mail.gmail.com> <11650.1238033714@sss.pgh.pa.us> Date: Wed, 25 Mar 2009 22:19:13 -0500 Message-ID: <7fd310d10903252019p22352555lc346b7532edf075c@mail.gmail.com> Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan From: Bryan Murphy To: Tom Lane Cc: Josh Berkus , 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=0.333 tagged_above=0 required=5 tests=AWL=0.333 X-Spam-Level: X-Archive-Number: 200903/373 X-Sequence-Number: 33295 On Wed, Mar 25, 2009 at 9:15 PM, Tom Lane wrote: > I think what you should be doing is messing with the cost parameters > ... and not in the direction you tried before. =A0I gather from > =A0 =A0 =A0 =A0effective_cache_size =3D 12GB > that you have plenty of RAM on this machine. =A0If the tables involved > are less than 1GB then it's likely that you are operating in a fully > cached condition, and the default cost parameters are not set up for > that. =A0You want to be charging a lot less for page accesses relative to > CPU effort. =A0Try reducing both seq_page_cost and random_page_cost to 0.= 5 > or even 0.1. =A0You'll need to watch your other queries to make sure > nothing gets radically worse though ... > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane Thanks Tom, I think that did the trick. I'm going to have to keep an eye on the database for a few days to make sure there are no unintended consequences, but it looks good. Here's the new query plan: HashAggregate (cost=3D40906.58..40906.67 rows=3D7 width=3D37) (actual time=3D204.661..204.665 rows=3D4 loops=3D1) -> Nested Loop (cost=3D0.00..40906.55 rows=3D7 width=3D37) (actual time=3D0.293..204.628 rows=3D11 loops=3D1) -> Nested Loop (cost=3D0.00..40531.61 rows=3D1310 width=3D70) (actual time=3D0.261..113.576 rows=3D3210 loops=3D1) -> Nested Loop (cost=3D0.00..39475.97 rows=3D1310 width=3D37) (actual time=3D0.232..29.484 rows=3D3210 loops=3D1) -> Index Scan using visitors_userid_index2 on visitors v (cost=3D0.00..513.83 rows=3D1002 width=3D33) (actual time=3D0.056..2.307 rows=3D899 loops=3D1) Index Cond: (userid =3D 'fbe2537f21d94f519605612c0bf7c2c5'::bpchar) -> Index Scan using itemexperiencelog__index__visitorid on itemexperiencelog l (cost=3D0.00..37.43 rows=3D116 width=3D70) (actual time=3D0.013..0.021 rows= =3D4 loops=3D899) Index Cond: (l.visitorid =3D v.id) -> Index Scan using items_primary_pkey on items_primary p (cost=3D0.00..0.79 rows=3D1 width=3D66) (actual time=3D0.018..0.019 rows= =3D1 loops=3D3210) Index Cond: (p.id =3D l.itemid) -> Index Scan using feeds_pkey on feeds f (cost=3D0.00..0.27 rows=3D1 width=3D33) (actual time=3D0.023..0.023 rows=3D0 loops=3D3210) Index Cond: (f.id =3D p.feedid) Filter: (lower((f.slug)::text) =3D 'wealth_building_by_nightingaleconant'::text) Total runtime: 204.759 ms What I did was change seq_page_cost back to 1.0 and then changed random_page_cost to 0.5 This also makes logical sense to me. We've completely rewritten our caching layer over the last three weeks, and introduced slony into our architecture, so our usage patterns have transformed overnight. Previously we were very i/o bound, now most of the actively used data is actually in memory. Just a few weeks ago there was so much churn almost nothing stayed cached for long. This is great, thanks guys! Bryan