Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 28261632D1D for ; Wed, 11 Mar 2009 00:37:35 -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 44233-03 for ; Wed, 11 Mar 2009 00:37:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f168.google.com (mail-fx0-f168.google.com [209.85.220.168]) by mail.postgresql.org (Postfix) with ESMTP id DA1B6632B4A for ; Wed, 11 Mar 2009 00:37:31 -0300 (ADT) Received: by fxm12 with SMTP id 12so2710763fxm.19 for ; Tue, 10 Mar 2009 20:37:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:mime-version:sender:reply-to:received :in-reply-to:references:date:x-google-sender-auth:message-id:subject :from:to:content-type; bh=TFQdub880pNu/1w4vHHmqhzqWumH3UhguApKQB9SdTc=; b=yCXUdbC51QjbRtfppqWP9gVIyX7YbT2sDeSE5wWc+1H2GyCvxx/FFF+QF2lYSrwwae Mf96qxSTzWpsFV52GtyT+BNOUKpgz3fOLK/IRxeaAi+5hOj3a/BL3jJtRnTqrOhAMDs0 nJ8Azxs+9UdgGDrk4Cyy1s29jgbkYH2BjVh3s= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=mime-version:sender:reply-to:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:content-type; b=t5CHoT0xSRfJQYhHFbP2tyuAIfJ34FvFU8uemND3p7L848SI3+iNpIa1VNz2QmvR2O dKizEHH8ZcxlpCL4Ryzs7ttRgh3ZCnrcCE72qGsAHeY0L0i5s4OKSY9FDkt+I0ZLR16L ItArARsz0E8e7ykVXWuJ/WJFGZLkNTtUNvHX0= MIME-Version: 1.0 Reply-To: smclellan@mintel.com Received: by 10.223.111.134 with SMTP id s6mr6047758fap.60.1236742232649; Tue, 10 Mar 2009 20:30:32 -0700 (PDT) In-Reply-To: References: Date: Tue, 10 Mar 2009 22:30:32 -0500 X-Google-Sender-Auth: de6f9998412a42e9 Message-ID: Subject: Re: Query performance over a large proportion of data From: Steve McLellan To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=001636c5a50174b1a60464cf7dbf X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/92 X-Sequence-Number: 33014 --001636c5a50174b1a60464cf7dbf Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit > > > > *"Kevin Grittner" * > 03/10/2009 05:06 PM EST > > > enable_seqscan = off > > Not a good idea; some queries will optimize better with seqscans. > You can probably get the behavior you want using other adjustments. > The bullet to cure the headache, as Scott said. > > You probably need to reduce random_page_cost. If your caching is > complete enough, you might want to set it equal to seq_page_cost > (never set it lower that seq_page_cost!) and possibly reduce both of > these to 0.1. > > Reducing seq_page_cost relative to random_page_cost seems to make an enormous difference for this query. Removing the nested loop seems to be what makes a difference. We'll continue to play with these and check there are no adverse effects on other queries. Thanks again, Steve --001636c5a50174b1a60464cf7dbf Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable



"Kevin Grittner" <Kevin.Grittne= r@wicourts.gov>
03/10/2009 05:06 PM EST

> enable_seqscan = =3D off

Not a good idea; som= e queries will optimize better with seqscans.
You can probably get the behavior you want using other adjustments.

The bullet to cure the headache, as Scott said.=A0=


You probably need to reduce ra= ndom_page_cost. =A0If your caching is
complete enough, you might want to= set it equal to seq_page_cost
(never set it lower that seq_page_cost!) and possibly reduce both of
the= se to 0.1.

Reducing seq_page_cost relative to random_page_cost seems to m= ake an enormous difference for this query. Removing the nested loop seems t= o be what makes a difference. We'll continue to play with these and che= ck there are no adverse effects on other queries.

Thanks again, Steve
--001636c5a50174b1a60464cf7dbf--