Skip site navigation (1) Skip section navigation (2)

LIKE optimization

From: Nabil Sayegh <nsmail(at)sayegh(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: LIKE optimization
Date: 2001-01-16 16:18:43
Message-ID: 3A647463.B166BDC8@sayegh.de (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

I'm using 7.0.3 and would like to know if there is a way to optimize
... where col1 LIKE "%" ...

with LIKE "%"
select h.user_id as hotel_id, h.m1_sterne as sterne, h.m1_hotel as
hotel, h.m1_ort as ort, h.m1_plz as plz, h.m1_region as region,
sum(k.preis * 1) as preis from hotels h, best_EZ k where h.user_id =
k.hotel_id and k.datum between '2001-02-01'::date and '2001-02-15'::date
- 1 and k.datum>now() and k.menge - k.reserviert - k.gebucht>=1 and
h.m1_region like 'Deutschland %' and h.m1_plz like '%' and h.m1_ort like
'%' and h.m1_sterne like '%' group by h.user_id, h.m1_hotel,
h.m1_sterne, h.m1_ort, h.m1_plz, h.m1_region, h.user_id having
count(*)>=14;


without LIKE "%"
select h.user_id as hotel_id, h.m1_sterne as sterne, h.m1_hotel as
hotel, h.m1_ort as ort, h.m1_plz as plz, h.m1_region as region,
sum(k.preis * 1) as preis from hotels h, best_EZ k where h.user_id =
k.hotel_id and k.datum between '2001-02-01'::date and '2001-02-15'::date
- 1 and k.datum>now() and k.menge - k.reserviert - k.gebucht>=1 and
h.m1_region like 'Deutschland %' group by h.user_id, h.m1_hotel,
h.m1_sterne, h.m1_ort, h.m1_plz, h.m1_region, h.user_id having
count(*)>=14;


Although the two queries should be equivalent the second takes about 4s
and the first takes almost 2 MINUTES.
I optimized this query manually by recognizing the special cases in my
program, but this shouldn't happen.

-- 
 Nabil Sayegh
 GPG-Key available at http://www.sayegh.de 
 (see http://www.gnupg.org for details)

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2001-01-16 16:19:22
Subject: Re: log file?
Previous:From: TamsinDate: 2001-01-16 15:59:21
Subject: RE: log file?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group