X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E299E52935 for ; Thu, 11 Aug 2005 00:15:03 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15238-08 for ; Thu, 11 Aug 2005 03:14:56 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7D9D152BE5 for ; Thu, 11 Aug 2005 00:14:53 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j7B3Eex3017715; Wed, 10 Aug 2005 23:14:40 -0400 (EDT) To: Tobias Brox Cc: pgsql-performance@postgresql.org Subject: Re: partial index regarded more expensive In-reply-to: <20050810175208.GD6141@tobias.lan> References: <20050810175208.GD6141@tobias.lan> Comments: In-reply-to Tobias Brox message dated "Wed, 10 Aug 2005 19:52:08 +0200" Date: Wed, 10 Aug 2005 23:14:40 -0400 Message-ID: <17714.1123730080@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/105 X-Sequence-Number: 13852 Tobias Brox writes: > This query puzzles me: > select * from game where game_end>'2005-07-30' and state in (3,4); > ... > Now, how can the planner believe the game_by_state-index to be better? I suspect the problem has to do with lack of cross-column statistics. The planner does not know that state=4 is correlated with game_end, and it's probably coming up with some bogus guesses about the numbers of index rows visited in each case. You haven't given enough info to quantify this, though. regards, tom lane