Re: LIKE optimization

your using table.attrib like '%' to match everything except nulls
right?

use table.attribute IS NOT NULL instead.

--rob

----- Original Message -----
From: "Nabil Sayegh" <nsmail(at)sayegh(dot)de>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Tuesday, January 16, 2001 11:18 AM
Subject: LIKE optimization

> 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)
>