X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2AC1E475AF8 for ; Mon, 31 Mar 2003 13:54:02 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 762B947580B for ; Mon, 31 Mar 2003 13:53:58 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h2VIrs2L024342; Mon, 31 Mar 2003 13:53:54 -0500 (EST) To: "scott.marlowe" Cc: Andreas Kostyrka , pgsql-performance@postgresql.org Subject: Re: Index not used, performance problem In-reply-to: References: Comments: In-reply-to "scott.marlowe" message dated "Mon, 31 Mar 2003 11:21:45 -0700" Date: Mon, 31 Mar 2003 13:53:54 -0500 Message-ID: <24341.1049136834@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-32.5 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/202 X-Sequence-Number: 1500 "scott.marlowe" writes: > So, create your index this way to make it smaller and faster: > create index dxname on sometable (bool_field) where bool_field IS TRUE; Also note that the index itself could be on some other column; for example if you do create index fooi on foo (intcol) where boolcol; then a query like select ... from foo where intcol >= 42 and boolcol; could use the index to exploit both WHERE conditions. > You have to, however, access it the same way. the proper > way to reference a bool field is with IS [NOT] {TRUE|FALSE} This strikes me as pedantry. "WHERE bool" (resp. "WHERE NOT bool") has the same semantics and is easier to read, at least to me. (Of course, if you think differently, then by all means write the form that seems clearest to you.) But yeah, the condition appearing in the actual queries had best match what's used in the partial-index CREATE command exactly. The planner is not real smart about deducing "this implies that". regards, tom lane