Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5EAF463344F for ; Mon, 6 Apr 2009 11:51:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57299-06 for ; Mon, 6 Apr 2009 11:50:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from megafon.hr (megafon.hr [213.202.104.5]) by mail.postgresql.org (Postfix) with ESMTP id 008A6633365 for ; Mon, 6 Apr 2009 11:50:58 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by megafon.hr (Postfix) with ESMTP id B2A3A2500A2; Mon, 6 Apr 2009 16:50:56 +0200 (CEST) X-Virus-Scanned: ClamAV at megafon.hr Received: from megafon.hr ([127.0.0.1]) by localhost (localhost.localdomain [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id FAKCPirMcqLC; Mon, 6 Apr 2009 16:50:49 +0200 (CEST) Received: from [192.168.1.94] (unknown [212.92.194.18]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: mike) by megafon.hr (Postfix) with ESMTP id E4FB52500A1; Mon, 6 Apr 2009 16:50:48 +0200 (CEST) Message-ID: <49DA16C9.8050405@megafon.hr> Date: Mon, 06 Apr 2009 16:50:49 +0200 From: Mario Splivalo User-Agent: Thunderbird 2.0.0.21 (X11/20090318) MIME-Version: 1.0 To: Scott Marlowe CC: Tom Lane , pgsql-performance@postgresql.org Subject: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance References: <49D0D237.7020308@megafon.hr> <3028.1238422571@sss.pgh.pa.us> <49D0E687.50605@megafon.hr> <49D9F39F.40106@megafon.hr> <49DA13C1.2030200@megafon.hr> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200904/132 X-Sequence-Number: 33499 Scott Marlowe wrote: >> CREATE INDEX photo_info_data_ix_field_value >> ON user_info_data USING btree (field_value); >> >> So, there is index on (user_id, field_name). Postgres is using index for >> user_id (...WHERE user_id = 12345) but not on field-name (...WHERE >> field_name = 'f-spot'). When I add extra index on field name: >> >> CREATE INDEX photo_info_data_ix__field_name >> ON 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. On 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) ? Mike