pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to force Postgres to use index on ILIKE
4+ messages / 3 participants
[nested] [flat]
* How to force Postgres to use index on ILIKE
@ 2006-06-05 19:29 Andrus <eetasoft@online.ee>
2006-06-05 21:26 ` Re: How to force Postgres to use index on ILIKE Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andrus @ 2006-06-05 19:29 UTC (permalink / raw)
To: pgsql-performance
I have UTF-8 Postgres 8.1 database on W2K3
Query
SELECT toode, nimetus
FROM toode
WHERE toode ILIKE 'x10%' ESCAPE '!'
ORDER BY UPPER(toode ),nimetus LIMIT 100
runs 1 minute in first time for small table size.
Toode field type is CHAR(20)
How to create index on toode field so that query can use it ?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to force Postgres to use index on ILIKE
2006-06-05 19:29 How to force Postgres to use index on ILIKE Andrus <eetasoft@online.ee>
@ 2006-06-05 21:26 ` Josh Berkus <josh@agliodbs.com>
2006-06-06 09:57 ` Re: How to force Postgres to use index on ILIKE Andrus <eetasoft@online.ee>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2006-06-05 21:26 UTC (permalink / raw)
To: pgsql-performance; +Cc: Andrus <eetasoft@online.ee>
Andrus,
> SELECT toode, nimetus
> FROM toode
> WHERE toode ILIKE 'x10%' ESCAPE '!'
> ORDER BY UPPER(toode ),nimetus LIMIT 100
>
> runs 1 minute in first time for small table size.
>
> Toode field type is CHAR(20)
1) why are you using CHAR and not VARCHAR or TEXT? CHAR will give you
problems using an index, period.
2) You can't use an index on ILIKE. You can, however, use an index on
lower(field) if your query is properly phrased and if you've created an
expression index on lower(field).
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to force Postgres to use index on ILIKE
2006-06-05 19:29 How to force Postgres to use index on ILIKE Andrus <eetasoft@online.ee>
2006-06-05 21:26 ` Re: How to force Postgres to use index on ILIKE Josh Berkus <josh@agliodbs.com>
@ 2006-06-06 09:57 ` Andrus <eetasoft@online.ee>
2006-06-06 14:23 ` Re: How to force Postgres to use index on ILIKE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Andrus @ 2006-06-06 09:57 UTC (permalink / raw)
To: pgsql-performance
>> SELECT toode, nimetus
>> FROM toode
>> WHERE toode ILIKE 'x10%' ESCAPE '!'
>> ORDER BY UPPER(toode ),nimetus LIMIT 100
>>
>> runs 1 minute in first time for small table size.
>>
>> Toode field type is CHAR(20)
>
> 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
2. I have a lot of existing code. Changing CHAR to VARCHAR requires probably
re-writing a lot of code, a huge work.
> 2) You can't use an index on ILIKE.
I'ts very sad. I expected that lower(toode) index can be used.
> You can, however, use an index on
> lower(field) if your query is properly phrased and if you've created an
> expression index on lower(field).
I tried by Postgres does not use index. Why ?
create index nimib2 on firma1.klient(lower(nimi) bpchar_pattern_ops);
explain analyze select nimi from firma1.klient where lower(nimi) like
'mokter%'
"Seq Scan on klient (cost=0.00..9.79 rows=1 width=74) (actual
time=0.740..0.761 rows=1 loops=1)"
" Filter: (lower((nimi)::text) ~~ 'mokter%'::text)"
"Total runtime: 0.877 ms"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to force Postgres to use index on ILIKE
2006-06-05 19:29 How to force Postgres to use index on ILIKE Andrus <eetasoft@online.ee>
2006-06-05 21:26 ` Re: How to force Postgres to use index on ILIKE Josh Berkus <josh@agliodbs.com>
2006-06-06 09:57 ` Re: How to force Postgres to use index on ILIKE Andrus <eetasoft@online.ee>
@ 2006-06-06 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-06-06 14:23 UTC (permalink / raw)
To: Andrus <eetasoft@online.ee>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-06-06 14:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-05 19:29 How to force Postgres to use index on ILIKE Andrus <eetasoft@online.ee>
2006-06-05 21:26 ` Josh Berkus <josh@agliodbs.com>
2006-06-06 09:57 ` Andrus <eetasoft@online.ee>
2006-06-06 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
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