Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA00551 for ; Tue, 15 Jun 1999 15:57:56 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by trends.net (8.8.8/8.8.8) with ESMTP id PAA20188 for ; Tue, 15 Jun 1999 15:57:53 -0400 (EDT) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id PAA23419; Tue, 15 Jun 1999 15:55:59 -0400 (EDT) To: Bruce Momjian cc: Howie , pgsql-sql@postgreSQL.org Subject: Re: [SQL] optimizer woes ? In-reply-to: Your message of Tue, 15 Jun 1999 13:13:18 -0400 (EDT) <199906151714.NAA11734@candle.pha.pa.us> Date: Tue, 15 Jun 1999 15:55:58 -0400 Message-ID: <23416.929476558@sss.pgh.pa.us> From: Tom Lane >> shouldn't the optimizer convert lower('anick') to lowercase first, then >> use that value in searching the logins_nick_idx index ? The system only knows how to use qualifications like "var rel constant" as index-scan restrictions. "var rel func(constant)" is not of that form ... but it could be if there were a preprocessing step that recognized "func(constant)" as a constant subexpression and replaced it by its result. Right now, we don't have any such step. I've been thinking about it for 6.6 though. Bruce Momjian writes: > We have on our TODO list: > * Use index with constants on functions The TODO entry is insufficiently ambitious: it should read "implement a general-purpose constant-subexpression-reduction step". (Actually, I think that TODO entry might refer to something completely different ... wasn't the complaint that you couldn't make an index on "date_part('date', field)"? ) regards, tom lane