Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id ACE94632E78 for ; Mon, 6 Apr 2009 12:23:20 -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 86342-09 for ; Mon, 6 Apr 2009 12:22:51 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f167.google.com (mail-ew0-f167.google.com [209.85.219.167]) by mail.postgresql.org (Postfix) with ESMTP id 35F6A632A8B for ; Mon, 6 Apr 2009 12:22:50 -0300 (ADT) Received: by ewy11 with SMTP id 11so2991090ewy.19 for ; Mon, 06 Apr 2009 08:22:50 -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=pRxCySApDfl2kqyESeCVVCO6ZypFhn/ZeP3dlq5excw=; b=HJAF72y3VY+QgUV3L3CPTTugX2un7E4ag5dYswuVsBYf3lkxLJdWfHAfX+YsTGuJ8X rrVdssbsgFlaNGSppwlFWR4rCEx9FtnChc/8206gNYwl9bPBOmU5pr8aHEwOHGAq/Hsi sVM4zWPvTmWpC0gigXcHZYOmStILzjkupdauM= 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=kaKzMBI05QoL4zWDbi0My8JvC3RXfz6/GVSK4/ALM1PONjFBPtP/mToDf+/lkbse7H jfpvZzC79IG+i6V40aLyCpRp4jbYZJhX7siYaELqaOVDso79IFFmbogBO47PrHWUoCaP 0jsisymnDhlvAiSKUxXJZymAXCq6724kt2NrE= MIME-Version: 1.0 Received: by 10.210.90.20 with SMTP id n20mr3025901ebb.77.1239031370067; Mon, 06 Apr 2009 08:22:50 -0700 (PDT) In-Reply-To: <49DA16C9.8050405@megafon.hr> References: <49D0D237.7020308@megafon.hr> <3028.1238422571@sss.pgh.pa.us> <49D0E687.50605@megafon.hr> <49D9F39F.40106@megafon.hr> <49DA13C1.2030200@megafon.hr> <49DA16C9.8050405@megafon.hr> Date: Mon, 6 Apr 2009 09:22:50 -0600 Message-ID: Subject: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance From: Scott Marlowe To: Mario Splivalo Cc: Tom Lane , 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.003 tagged_above=0 required=5 tests=AWL=0.003 X-Spam-Level: X-Archive-Number: 200904/133 X-Sequence-Number: 33500 On Mon, Apr 6, 2009 at 8:50 AM, Mario Splivalo wrote: > Scott Marlowe wrote: >>> >>> CREATE INDEX photo_info_data_ix_field_value >>> =A0ON user_info_data USING btree (field_value); >>> >>> So, there is index on (user_id, field_name). Postgres is using index fo= r >>> user_id (...WHERE user_id =3D 12345) but not on field-name (...WHERE >>> field_name =3D 'f-spot'). When I add extra index on field name: >>> >>> CREATE INDEX photo_info_data_ix__field_name >>> =A0ON user_info_data USING btree (field_name); >>> >>> Then that index is used. >> >> On older versions of pgsql, the second of two terms in a multicolumn >> index can't be used alone. =A0On newer versions it can, but it is much >> less efficient than if it's a single column index or if the term is >> the first one not the second. > > I'm using 8.3.7. So, you'd also suggest to keep that extra (in a way > redundant) index on field_name, since I need PK on (photo_id, field_name)= ? Either that or reverse the terms in the pk. Also, you might want to look at adjusting random_page_access to something around 1.5 to 2.0.