Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 1BAD49F98CD for ; Fri, 31 Aug 2007 18:56:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 55273-05 for ; Fri, 31 Aug 2007 18:56:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8151F9F9897 for ; Fri, 31 Aug 2007 18:56:29 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l7VLoDQ6002171; Fri, 31 Aug 2007 17:50:13 -0400 (EDT) To: Michael Glaesemann cc: Richard Ray , pgsql-sql@postgresql.org Subject: Re: How to influence the planner In-reply-to: References: <373D2F52-E9CD-485A-9BF1-B019349FA5EC@seespotcode.net> Comments: In-reply-to Michael Glaesemann message dated "Fri, 31 Aug 2007 16:38:20 -0500" Date: Fri, 31 Aug 2007 17:50:13 -0400 Message-ID: <2170.1188597013@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/274 X-Sequence-Number: 29212 Michael Glaesemann writes: > On Aug 31, 2007, at 16:07 , Richard Ray wrote: >>> If length(bar) = 0 is a common operation on this table, you might >>> consider using an expression index on t1: >> >>> create index t1_length_bar_idx on t1 (length(bar)); >> >> This is a one time procedure to fix some data but I've had this >> problem before Actually, I just noticed that the OP does have an index on bar, which means (assuming it's a string data type) that this query is equivalent to select * from t1 where bar = '' which would be a far preferable way to do it because that condition can use the index. The Postgres planner is fairly data-type-agnostic and does not have the knowledge that these are equivalent queries, so you can't expect it to make that substitution for you. regards, tom lane