X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 223B19FA2E6 for ; Tue, 6 Jun 2006 11:24:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 01742-01 for ; Tue, 6 Jun 2006 11:24:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 72BDC9F9316 for ; Tue, 6 Jun 2006 11:24:00 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k56ENuaM016927; Tue, 6 Jun 2006 10:23:56 -0400 (EDT) To: "Andrus" cc: pgsql-performance@postgresql.org Subject: Re: How to force Postgres to use index on ILIKE In-reply-to: References: <200606051426.56789.josh@agliodbs.com> Comments: In-reply-to "Andrus" message dated "Tue, 06 Jun 2006 12:57:31 +0300" Date: Tue, 06 Jun 2006 10:23:55 -0400 Message-ID: <16926.1149603835@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/51 X-Sequence-Number: 19408 "Andrus" writes: >> 1) why are you using CHAR and not VARCHAR or TEXT? CHAR will give you >> problems using an index, period. > 1. I haven't seen any example where VARCHAR is better that CHAR for indexing The advice you were given is good, even if the explanation is bad. CHAR(n) is a poor choice for just about every purpose, because of all the padding blanks it insists on storing and transmitting. That adds up to a lot of wasted space, I/O effort, and CPU cycles. > I tried by Postgres does not use index. Why ? > create index nimib2 on firma1.klient(lower(nimi) bpchar_pattern_ops); Try to get over this fixation on CHAR. That would work with text_pattern_ops --- lower() returns TEXT, and TEXT is what the LIKE operator accepts, so that's the opclass you need to use to optimize lower() LIKE 'pattern'. regards, tom lane