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 3F6DF3A3CC0 for ; Fri, 5 Nov 2004 14:39:23 +0000 (GMT) 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 30681-04 for ; Fri, 5 Nov 2004 14:39:13 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.198]) by svr1.postgresql.org (Postfix) with ESMTP id EC77F3A3C95 for ; Fri, 5 Nov 2004 14:39:16 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 58so58594wri for ; Fri, 05 Nov 2004 06:39:16 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=OGBvXYMkJYEYLsk4Lm7Vzl2EBLK4kTP3/IEt5HCDiJndzGvSF/r+ZL5Ez4TbWfxAUDUfmlGcvrv1ZIstLT73dPB4K70KMat5XOveQ9NkKjEcK3kynE3jiw3OiWMpD5YES2HCqwSj670w6HqAJlvDpxaVdeK0mBGv2k++gMYEBRU= Received: by 10.54.21.71 with SMTP id 71mr22982wru; Fri, 05 Nov 2004 06:39:16 -0800 (PST) Received: by 10.54.43.36 with HTTP; Fri, 5 Nov 2004 06:39:16 -0800 (PST) Message-ID: <88f1825a041105063955367088@mail.gmail.com> Date: Fri, 5 Nov 2004 09:39:16 -0500 From: Allen Landsidel Reply-To: Allen Landsidel To: pgsql-performance@postgresql.org Subject: Strange (?) Index behavior? Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/94 X-Sequence-Number: 9048 Hey people, long while since I posted here, but I'm having an index issue that looks on the surface to be a little strange. I have a text field that I'm trying to query on in a table with millions of rows. Stupid I know, but a fairly common stupid thing to try to do. For some reason it's a requirement that partial wildcard searches are done on this field, such as "SELECT ... WHERE field LIKE 'A%'" I thought an interesting way to do this would be to simply create partial indexes for each letter on that field, and it works when the query matches the WHERE clause in the index exactly like above. The problem is thus: Say I have an index.. CREATE INDEX column_idx_a ON table (column) WHERE column LIKE 'A%' It seems to me that a query saying "SELECT column FROM table WHERE column LIKE 'AA%';" should be just as fast or very close to the first case up above. However, explain tells me that this query is not using the index above, which is what's not making sense to me. Does the planner not realize that 'AA%' will always fall between 'A%' and 'B', and thus that using the index would be the best way to go, or am I missing something else that's preventing this from working?