Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 58A6C633611 for ; Fri, 19 Mar 2010 23:16:10 -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 70436-10 for ; Sat, 20 Mar 2010 02:15:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 927E063282C for ; Fri, 19 Mar 2010 23:15:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o2K2FulS028833; Fri, 19 Mar 2010 22:15:56 -0400 (EDT) To: Alexandre de Arruda Paes cc: pgsql-performance@postgresql.org Subject: Re: PG using index+filter instead only use index In-reply-to: References: <5135.1269028162@sss.pgh.pa.us> Comments: In-reply-to Alexandre de Arruda Paes message dated "Fri, 19 Mar 2010 18:04:46 -0300" Date: Fri, 19 Mar 2010 22:15:56 -0400 Message-ID: <28832.1269051356@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.461 tagged_above=-10 required=5 tests=AWL=0.138, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/251 X-Sequence-Number: 38076 Alexandre de Arruda Paes writes: > 2010/3/19 Tom Lane : >> The cost estimates look a bit unusual to me; are you using nondefault >> cost parameters, and if so what are they? > The non default value in cost parameters is different only in > random_page_cost that are set to 2.5 and default_statistics_target set > to 300. Okay, so with random_page_cost = 2.5, those cost estimates definitely indicate that it's expecting only one heap tuple to be visited, for either choice of index. > I don't know why the planner prefer to use a less specific index > (ict14t1) and do a filter than use an index that matches with the > WHERE parameter... The cost estimate formulas bias the system against using a larger index when a smaller one will do. That seven-column index is probably at least twice as large as the two-column index, so it's hardly unreasonable to assume that scanning it will take more I/O and cache space and CPU time than using a smaller index, if all else is equal. Now of course all else is not equal if the smaller index is less selective than the larger one, but the cost estimates indicate that the planner thinks the two-column index condition is sufficient to narrow things down to only one heap tuple anyway. The fact that the smaller index is actually slower indicates that this estimate is off, ie (ct07emp01 = 2) AND (ct07c_cust = 0) actually selects more than one heap tuple. It's hard to speculate about why that estimate is wrong on the basis of the information you've shown us though. Perhaps there is a strong correlation between the values of those two columns? regards, tom lane