From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrus <eetasoft@online.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to force Postgres to use index on ILIKE
Date: Tue, 06 Jun 2006 10:23:55 -0400
Message-ID: <16926.1149603835@sss.pgh.pa.us> (raw)
In-Reply-To: <e63jit$28fk$2@news.hub.org>
References: <e620mg$15jr$1@news.hub.org>
<200606051426.56789.josh@agliodbs.com>
<e63jit$28fk$2@news.hub.org>
"Andrus" <eetasoft@online.ee> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, eetasoft@online.ee
Subject: Re: How to force Postgres to use index on ILIKE
In-Reply-To: <16926.1149603835@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox