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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-01-16 16:19:22 | Re: log file? |
Previous Message | Tamsin | 2001-01-16 15:59:21 | RE: log file? |